 indexes sql tunning [message #131660] |
Mon, 08 August 2005 19:29  |
quetzalcoatl
Messages: 5
Registered: August 2005
|
Junior Member |
|
|
Hi all
I have table0 and table1 (they both contains millions of registers) , they are almost the same, just the difference that table1 contains less registers than table0, but as you can see in the code the access for the table0 which contains much more registers is faster than table1, i have indexes in they both, but with this information can you give me an idea about how to enhance access to table1
Or you can give me a URL where i can find how to interpretate exactly this information.
ThanX
SELECT
table0.*
FROM
table0,anothertable,anothertable2
WHERE
tipoventa = 1
AND fechad BETWEEN TO_DATE('01/08/2005','dd/mm/yyyy') AND TO_DATE('07/08/2005','dd/mm/yyyy')
AND table0.sucursal = anothertable.cvesucursal
AND table0.region = anothertable.cveregion
AND table0.area = anothertable.cvearea
AND table0.fipaisid = anothertable.fipaisid
AND table0.mil_linea = anothertable2.mil_linea
AND table0.mil_sublinea = anothertable2.mil_sublinea
AND table0.mil_clase = anothertable2.mil_clase
AND anothertable2.MIL_TIPO = 'MILENIA'
AND anothertable2.mil_plan IN ('2','3','4')
AND saz_catgeografia.milenia = '1'
706 rows selected.
Elapsed: 00:00:02.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5338 Card=1 Bytes=126)
1 0 NESTED LOOPS (Cost=5338 Card=1 Bytes=126)
2 1 HASH JOIN (Cost=5337 Card=1 Bytes=104)
3 2 INDEX (FAST FULL SCAN) OF 'SAZ_1' (NON-UNIQUE) (Cost=3 Card=875 Bytes=20125)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MIL_GRANO0' (Cost=5333 Card=9118 Bytes=738558)
5 4 INDEX (SKIP SCAN) OF 'MILF1' (NON-UNIQUE) (Cost=83 Card=94)
6 1 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF 'SAZ_C4' (NON-UNIQUE) (Cost=1 Card=1 Bytes=22)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81373 consistent gets
0 physical reads
0 redo size
30513 bytes sent via SQL*Net to client
604 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
706 rows processed
SELECT
/*+ index(table1 idx2) */
table1.*
FROM
table1,anothertable,anothertable2
WHERE
tipoventa = 1
AND fechad BETWEEN TO_DATE('01/08/2005','dd/mm/yyyy') AND TO_DATE('07/08/2005','dd/mm/yyyy')
AND table1.sucursal = anothertable.cvesucursal
AND table1.region = anothertable.cveregion
AND table1.area = anothertable.cvearea
AND table1.fipaisid = anothertable.fipaisid
AND table1.mil_linea = anothertable2.mil_linea
AND table1.mil_sublinea = anothertable2.mil_sublinea
AND table1.mil_clase = anothertable2.mil_clase
AND anothertable2.MIL_TIPO = 'MILENIA'
AND anothertable2.mil_plan IN ('2','3','4')
AND saz_catgeografia.milenia = '1'
706 rows selected.
Elapsed: 00:00:11.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=125)
1 0 NESTED LOOPS (Cost=9 Card=1 Bytes=125)
2 1 HASH JOIN (Cost=8 Card=1 Bytes=103)
3 2 INDEX (FAST FULL SCAN) OF 'SAZ_1' (NON-UNIQUE) (Cost=3 Card=875 Bytes=20125)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MIL_GRANO11' (Cost=4 Card=946 Bytes=75680)
5 4 INDEX (RANGE SCAN) OF 'MIL1101' (NON-UNIQUE) (Cost=2 Card=1)
6 1 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF 'SAZ_C4' (NON-UNIQUE) (Cost=1 Card=1 Bytes=22)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
101370 consistent gets
1222 physical reads
0 redo size
30412 bytes sent via SQL*Net to client
604 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
706 rows processed
SELECT
/*+ index(table1 idx2) */
table1.*
FROM
table1,anothertable,anothertable2
WHERE
tipoventa = 1
AND fechad BETWEEN TO_DATE('01/08/2005','dd/mm/yyyy') AND TO_DATE('07/08/2005','dd/mm/yyyy')
AND table1.sucursal = anothertable.cvesucursal
AND table1.region = anothertable.cveregion
AND table1.area = anothertable.cvearea
AND table1.fipaisid = anothertable.fipaisid
AND table1.mil_linea = anothertable2.mil_linea
AND table1.mil_sublinea = anothertable2.mil_sublinea
AND table1.mil_clase = anothertable2.mil_clase
AND anothertable2.MIL_TIPO = 'MILENIA'
AND anothertable2.mil_plan IN ('2','3','4')
AND saz_catgeografia.milenia = '1'
706 rows selected.
Elapsed: 00:00:15.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=125)
1 0 NESTED LOOPS (Cost=8 Card=1 Bytes=125)
2 1 HASH JOIN (Cost=7 Card=1 Bytes=103)
3 2 INDEX (FAST FULL SCAN) OF 'SAZ_1' (NON-UNIQUE) (Cost=3 Card=875 Bytes=20125)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MIL_GRANO11' (Cost=3 Card=946 Bytes=75680)
5 4 INDEX (RANGE SCAN) OF 'MIL1102' (NON-UNIQUE) (Cost=2 Card=946)
6 1 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF 'SAZ_C4' (NON-UNIQUE) (Cost=1 Card=1 Bytes=22)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66200 consistent gets
0 physical reads
0 redo size
28597 bytes sent via SQL*Net to client
604 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
706 rows processed
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
