Re: partitionning join table and normalization

From: <chmanu_at_gmail.com>
Date: Wed, 9 Apr 2008 01:52:25 -0700 (PDT)
Message-ID: <e4667beb-469a-40b4-8b00-5892ab3254f7@a1g2000hsb.googlegroups.com>

Hello again,

I do some tests and i hope having good advice to tune :

The tests are not complete because i only create the normalize table without alter the first table by dropping the unusable columns (i need to develop a migrate script).
and the new table contain no data in the data field. My first table (TD) : 947409 rows
The normalize table (NT) : 2490368 rows

The normalize table is a IOT (PK on ID_DATA ; FIELD_ID) and i add a index on ID_DATA (for the group by)

If i create the view (V_NT) the plan cost show me a proportionnal growth with number of NT rows inserted. (for select * ..where id=...) If i ask to oracle the same query as the view but precise inside the from of the view my ID, it fall down (1992 to 3) because use the index to do the view treatment only on the id i m interresting of.

So the question is :
Is it possible to say to oracle to do the treatment only on the id is return ?

• TRACE -----

select *
  from V_NT
 where id = 17259478
 Plan
SELECT STATEMENT ALL_ROWSCost : 1 992 Bytes : 277 585 605 Cardinality : 1 814 285

        3 HASH GROUP BY Cost : 1 992 Bytes : 277 585 605 Cardinality : 1 814 285

		2 FILTER
			1 INDEX FAST FULL SCAN TD_PK Cost : 1 879 Bytes : 277 585 605

Cardinality : 1 814 285

SELECT ACP_ID,

       MAX(DECODE(ID_FIELD, 1, DATA)) AS ded,
       MAX(DECODE(ID_FIELD, 2, DATA)) AS qsqd,
       MAX(DECODE(ID_FIELD, 3, DATA)) AS dsv,
       MAX(DECODE(ID_FIELD, 4, DATA)) AS ntrg,
       MAX(DECODE(ID_FIELD, 5, DATA)) AS yj,
       MAX(DECODE(ID_FIELD, 6, DATA)) AS dsq,
       MAX(DECODE(ID_FIELD, 7, DATA)) AS df,
       MAX(DECODE(ID_FIELD, 8, DATA)) AS ee,
       MAX(DECODE(ID_FIELD, 9, DATA)) AS er,
       MAX(DECODE(ID_FIELD, 10, DATA)) AS gf,
       MAX(DECODE(ID_FIELD, 11, DATA)) AS rezg,
       MAX(DECODE(ID_FIELD, 12, DATA)) AS f,
       MAX(DECODE(ID_FIELD, 13, DATA)) AS fds,
       MAX(DECODE(ID_FIELD, 14, DATA)) AS fdsv,
       MAX(DECODE(ID_FIELD, 15, DATA)) AS vf,
       MAX(DECODE(ID_FIELD, 16, DATA)) AS zz,
       MAX(DECODE(ID_FIELD, 17, DATA)) AS k_INF,
       MAX(DECODE(ID_FIELD, 18, DATA)) AS bvr,
       MAX(DECODE(ID_FIELD, 19, DATA)) AS d
  FROM (SELECT *
          FROM NT
         WHERE id = 17259478) tb

 where id = 17259478
 GROUP BY ID
 Plan
SELECT STATEMENT ALL_ROWSCost : 3 Bytes : 2 907 Cardinality : 19

	3 SORT GROUP BY NOSORT  Cost : 3 Bytes : 2 907 Cardinality : 19
		2 INDEX UNIQUE SCAN TD_PK Cost : 3 Bytes : 2 907 Cardinality : 19
			1 INDEX RANGE SCAN TD.IDX Cost : 3 Cardinality : 19

Thanks for your help.

Chmanu Received on Wed Apr 09 2008 - 03:52:25 CDT