Index Not Being Used – Fragmentation

I was experiencing FTS on SELECT for 1 record.

SQL> select A.vk_accn_test_seq_id, count(*) FROM accn_phys A group by A.vk_accn_test_seq_id;

VK_ACCN_TEST_SEQ_ID   COUNT(*)
------------------- ----------
            2268482          1
            3765572          1
            1038777          1
            2053978          1
            3639618          1
            3940603          1
             628110          1 +++
            2639137          1
                  0     469892

9 rows selected.

SQL> SELECT pk_seq_id FROM accn_phys A WHERE A.vk_accn_test_seq_id=628110;

 PK_SEQ_ID
----------
     95100

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 3167157579

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 51408 |   401K|   704   (0)| 07:27:00 |
|*  1 |  TABLE ACCESS FULL| ACCN_PHYS | 51408 |   401K|   704   (0)| 07:27:00 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."VK_ACCN_TEST_SEQ_ID"=628110)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4662  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select column_name, num_distinct, num_nulls, num_buckets, density, histogram
     from user_tab_col_statistics
     where table_name = 'ACCN_PHYS' and column_name='VK_ACCN_TEST_SEQ_ID'
     order by column_name;  2    3    4    5    6    7    8    9

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------- ---------------
VK_ACCN_TEST_SEQ_ID                       9          0           1 .111111111 NONE

SQL> select index_name from user_ind_columns where table_name='ACCN_PHYS' and column_name='VK_ACCN_TEST_SEQ_ID';

INDEX_NAME
------------------------------
I_ACCN_PHYS_ACCNTESTSEQID

SQL> analyze index I_ACCN_PHYS_ACCNTESTSEQID validate structure;

Index analyzed.

SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows from index_stats;

    HEIGHT     BLOCKS    LF_BLKS    LF_ROWS    BR_BLKS    BR_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
         3       2176       2100     471150          8       2099        1248

SQL>

NOTICE: the large number of DEL_LF_ROWS

On QA system: REBUILD Index

SQL> alter index I_ACCN_PHYS_ACCNTESTSEQID rebuild;

Index altered.

SQL> analyze index I_ACCN_PHYS_ACCNTESTSEQID validate structure;

Index analyzed.

SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows from index_stats;

    HEIGHT     BLOCKS    LF_BLKS    LF_ROWS    BR_BLKS    BR_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
         3        832        805     444305          3        804           0

SQL> SELECT pk_seq_id FROM accn_phys A WHERE A.vk_accn_test_seq_id=628110;

 PK_SEQ_ID
----------
     95100

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 234933029

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           | 63472 |   495K|   735   (1)| 00:00:09 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACCN_PHYS                 | 63472 |   495K|   735   (1)| 00:00:09 |
|*  2 |   INDEX RANGE SCAN          | I_ACCN_PHYS_ACCNTESTSEQID | 63472 |       |   120   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."VK_ACCN_TEST_SEQ_ID"=628110)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Advertisements

Create Date Dimension from SQL

Note to self.

SELECT 
  TO_NUMBER(TO_CHAR(dt,'YYYYMMDD')) date_key,
  dt date_value,
  TO_NUMBER(TO_CHAR(dt,'DD')) day,
  TO_NUMBER(TO_CHAR(dt,'MM')) month,
  TO_NUMBER(TO_CHAR(dt,'Q')) quarter,
  TO_NUMBER(TO_CHAR(dt,'YYYY')) year,
  TO_CHAR(dt,'DAY') AS day_name,
  TO_CHAR(dt,'MONTH') AS month_name,
  TO_NUMBER(TO_CHAR(dt,'D')) day_week,
  TO_NUMBER(TO_CHAR(dt,'DDD')) day_year,
  TO_NUMBER(TO_CHAR(dt+1,'W')) AS week_month,
  TO_NUMBER(TO_CHAR(dt+1,'WW')) AS week_year,
  TO_NUMBER(TO_CHAR(dt+1,'IW')) AS iso_week_year		 
FROM (  
  SELECT LEVEL n, TO_DATE('31/12/2010','DD/MM/YYYY')+NUMTODSINTERVAL(LEVEL,'day') dt
  FROM DUAL
  CONNECT BY LEVEL <= 365
)
ORDER BY 1 ASC;

Reference: Oracle Date and Time formats