Printable View
Quote:
Originally posted by DaPi
I don't see much point in re-wrting the query in detail, since we aren't quite sure what it actually is!
"Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query."
..hmm i quite really didnt see this part in Anand's Later post.;)
Yes, they should use the index, but only in so far as the index will (I think) find the rowid's of ALL the rows with COMPANY_ID = 8170926 because there is no condition on the second column in the index, PAYMENT_CLEARED (9i might do better, but this is 8.1.7). The 2nd & 3rd can be more selective because PAYMENT_CLEARED (and SHIPMENT_STATUS in the 3rd) are part of the condition.Quote:
Originally posted by abhaysk
I dont see any reason why 1st and 4th should not take better advantage of existing composite index. :confused:
many times Oracle rewrites OR into UNION ALL
Quote:
Originally posted by DaPi
Yes, they should use the index, but only in so far as the index will (I think) find the rowid's of ALL the rows with COMPANY_ID = 8170926 because there is no condition on the second column in the index, PAYMENT_CLEARED (9i might do better, but this is 8.1.7). The 2nd & 3rd can be more selective because PAYMENT_CLEARED (and SHIPMENT_STATUS in the 3rd) are part of the condition.
Ok, lemme give u an example of how best you can get from an index...
See above how in the second case Index Range Scan is confined to just FORDATE col, & in first case how is it benifited even when you have PIN at the 6th position & you can expect that Index has to search at the worst 3*5*9*7 keys to find the pin.Code:WW04_PRO> Select Column_Position, Index_Name, Table_Name, Substr(Column_Name, 1, 30) from dba_ind_columns
2 where table_name = 'YTD_MTD_SHIPMENTS';
COLUMN_POSITION INDEX_NAME TABLE_NAME SUBSTR(COLUMN_NAME,1,30)
-------------------- ------------------------------ ------------------------------ ------------------------------
1 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS FORDATE
2 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS REGION_CD
3 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS ORG_LEVEL_NO
4 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS PH_LEVEL_NO
5 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS PRODUCT_TYPE
6 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS PIN
7 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS ORG_CD
8 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS COMPANY_CD
8 rows selected.
WW04_PRO> ed
Wrote file afiedt.buf
1* Select Count(Distinct REGION_CD) from ytd_mtd_shipments
WW04_PRO> /
COUNT(DISTINCTREGION_CD)
------------------------
3
Elapsed: 00:00:01.01
WW04_PRO> ed
Wrote file afiedt.buf
1* Select Count(Distinct Org_Level_No) from ytd_mtd_shipments
WW04_PRO> /
COUNT(DISTINCTORG_LEVEL_NO)
---------------------------
5
Elapsed: 00:00:01.01
WW04_PRO> ed
Wrote file afiedt.buf
1* Select Count(Distinct Ph_Level_No) from ytd_mtd_shipments
WW04_PRO> /
COUNT(DISTINCTPH_LEVEL_NO)
--------------------------
9
Elapsed: 00:00:02.00
WW04_PRO> Select Count(Distinct Product_Type) from ytd_mtd_shipments;
COUNT(DISTINCTPRODUCT_TYPE)
---------------------------
7
Elapsed: 00:00:01.01
WW04_PRO> Select * from YTD_MTD_SHIPMENTS where FORDATE = '18-DEC-03' and PIN = 'DA236A#ABA';
FORDATE RE O P PR PIN ORG_CD
--------- -- - - -- ------------------ -----------------
18-DEC-03 AM 0 8 CM DA236A#ABA CORP
18-DEC-03 AM 1 8 CM DA236A#ABA AMERICAS
18-DEC-03 AM 2 8 CM DA236A#ABA LATIN AMERICA
18-DEC-03 AM 3 8 CM DA236A#ABA NMCA
18-DEC-03 AM 4 8 CM DA236A#ABA 18SP
Elapsed: 00:00:09.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=13 Bytes=1365)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'YTD_MTD_SHIPMENTS' (Cost=14 Card=13 Bytes=1365)
2 1 INDEX (RANGE SCAN) OF 'PK_YTD_MTD_SHIPMENTS' (UNIQUE) (Cost=9 Card=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2173 consistent gets
1431 physical reads
6134756 redo size
2472 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
WW04_PRO> ed
Wrote file afiedt.buf
1 Select * from
2 (Select * from YTD_MTD_SHIPMENTS where FORDATE = '18-DEC-03' and rownum > 0)
3* Where PIN = 'DA236A#ABA'
WW04_PRO> /
FORDATE RE O P PR PIN ORG_CD
--------- -- - - -- ------------------ ----------------------
18-DEC-03 AM 0 8 CM DA236A#ABA CORP
18-DEC-03 AM 1 8 CM DA236A#ABA AMERICAS
18-DEC-03 AM 2 8 CM DA236A#ABA LATIN AMERICA
18-DEC-03 AM 3 8 CM DA236A#ABA NMCA
18-DEC-03 AM 4 8 CM DA236A#ABA 18SP
Elapsed: 00:00:23.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=136 Card=1347 Bytes=449898)
1 0 VIEW (Cost=136 Card=1347 Bytes=449898)
2 1 COUNT
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'YTD_MTD_SHIPMENTS'(Cost=136 Card=1347 Bytes=141435)
5 4 INDEX (RANGE SCAN) OF 'PK_YTD_MTD_SHIPMENTS' (UNIQUE) (Cost=9 Card=539)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
139182 consistent gets
2674 physical reads
0 redo size
2352 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
WW04_PRO>
I think there is something more than what we have been told to us by DOCS.
Abhay.
Abhay, which version are you running?
9.2.0.4, but i dont think 8.x will do any bad....Quote:
Originally posted by DaPi
Abhay, which version are you running?
Abhay.
Version 9i has INDEX SKIP SCAN http://download-west.oracle.com/docs...mops.htm#51553 which did not exist before. I imagine that could be happening (even if the explain plan does not say so).
But the above eg is suerly not a skip scan for sure....:rolleyes: :rolleyes:Quote:
Originally posted by DaPi
Version 9i has INDEX SKIP SCAN http://download-west.oracle.com/docs...mops.htm#51553 which did not exist before. I imagine that could be happening (even if the explain plan does not say so).
Ok, Here goes for 8.x
You can see above only in last 2 cases it will be of no importance as it has to scan all the missed out keys obtained from COL postion 2 and 3...But If i see anandkl's composite key, theres only 2 cols that will not be used in sub qry 1 and 4 but again these cols in where clause are not at the end...and more over the card of shipment_status and PAYMENT_CLEARED are just 2 each so at the most you will be scanning 4 times more without including these 2 cols and with including...so i expect range scan will do better that not using.Code:U144> Select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Elapsed: 00:00:01.01
U144> select column_position, index_name, table_name, substr(column_name, 1,30) from dba_ind_columns
2 where table_name = 'RPT_PIT_TRANS_DATA' and index_name like 'PK%';
COLUMN_POSITION INDEX_NAME TABLE_NAME SUBSTR(COLUMN_NAME,1,30)
-------------------- ------------------------------ ------------------------------ ------------------------------
1 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA PLANT_CD
2 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA SUPPLIER_NO
3 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA MATERIAL_NO
4 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA EFFECTIVE_DT
Elapsed: 00:00:02.09
U144> Set autotrace on
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and MATERIAL_NO = '207074-101');
COUNT(*)
--------------------
308
Elapsed: 00:00:15.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=254 Card=1 Bytes=15)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_PIT_TRANS_DATA' (Cost=254 Card=2 Bytes=30)
3 2 INDEX (RANGE SCAN) OF 'AK4_RPT_PIT_TRANS_DATA' (NON-UNIQUE) (Cost=5 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
788 consistent gets
784 physical reads
0 redo size
369 bytes sent via SQL*Net to client
390 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and rownum > 0)
2 Where MATERIAL_NO = '207074-101';
COUNT(*)
--------------------
308
Elapsed: 00:01:06.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=1026 Card=131038 Bytes=1441418)
3 2 COUNT
4 3 FILTER
5 4 INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=131038 Bytes=1965570)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13973 consistent gets
13931 physical reads
1404 redo size
369 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and EFFECTIVE_DT = '20011112');
COUNT(*)
--------------------
8597
Elapsed: 00:01:01.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=66 Bytes=792)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13976 consistent gets
13932 physical reads
1456 redo size
369 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
U144> ed
Wrote file afiedt.buf
1 Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and rownum > 0)
2* Where EFFECTIVE_DT = '20011112'
U144> /
COUNT(*)
--------------------
8597
Elapsed: 00:01:07.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=1026 Card=131038 Bytes=786228)
3 2 COUNT
4 3 FILTER
5 4 INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=131038 Bytes=1572456)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13974 consistent gets
13930 physical reads
1464 redo size
369 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
U144>
Abhay.
Hi Friends,
Thank you all for the inputs, i am pasting the stats asked by Tamilselvan
Statistics
----------------------------------------------------------
889 recursive calls
121 db block gets
723827 consistent gets
693231 physical reads
700 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed