Home » RDBMS Server » Performance Tuning » indexes sql tunning
icon7.gif  indexes sql tunning [message #131660] Mon, 08 August 2005 19:29 Go to next message
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




Re: indexes sql tunning [message #131667 is a reply to message #131660] Mon, 08 August 2005 20:46 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
But the queries are not same!.
You are using hints in the later ones.
In both, a hash join involved. Do You really need index scan here?
Enable trace/statspack. You might see a lot of waits on sequential reads.

[Updated on: Mon, 08 August 2005 20:47]

Report message to a moderator

Previous Topic: how to split the query using rowid
Next Topic: SQL Analyze
Goto Forum:
  


Current Time: Thu Mar 28 08:48:28 CDT 2024