Performance Tuning :: Create Table Statement With Union?
Aug 5, 2010
this statement is taking 1hr , can we reduce the timing?
CREATE TABLE DGT_ITEMEFFORTDATA (ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE, SUPEROWNERID,
ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID, MONTHID,
QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS, STAGESTATUS,
OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
[code].....
This is the explain plan of the above query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 4137M| 46149 (
| 1 | UNION-ALL | | | |
| 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA_DAILY | 3455K| 428M| 14575
[code].....
This is the index details
1DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMID4
2DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMTYPE3
3DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERID2
4DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERTYPE1
There is no index on DGT_ITEMEFFORTDATA_TEMP table
[code].....
View 27 Replies
ADVERTISEMENT
May 18, 2010
Can we have same execution plan for a create table statement where the name of the table changes every time as follows:
create table test
as
select * from t1
Here table name changes from test to another table name next time
View 6 Replies
View Related
Oct 31, 2011
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
View 39 Replies
View Related
Oct 11, 2009
I m looking for any other way that i can code without using union all for my case.my data example is like this
table1
--------
col1 col2 Segment1 Segment2 Segment3
-------------------------------------------------------------
A11 B11 John Jhonny Johnathan
A12 B12 Melisa Amy Abagial
I need to create view of above record as below:
table2
------
col1 col2 col3 col4
--------------------------------------------------------------
A11 B11 Segment1 John
A11 B11 Segment2 Jhonny
A11 B11 Segment3 Johnathan
A12 B12 Segment1 Melisa
A12 B12 Segment2 Amy
A12 B12 Segment3 Abagial
now my code is using UNION ALL to get output as in table2
select col1,col1,'Segment1' col3,Segment1 col4 from table1
union all
select col1,col1,'Segment2' col3,Segment2 from table1
union all
select col1,col1,'Segment3' col3,Segment3 from table1
But the problem is the performance is realy bad.Is there any way i can do this without using union all? The time that take to execute this is not exceptable.
View 4 Replies
View Related
Sep 9, 2010
Oracle UNION ALL performance issue: when I try to run below SQL query separately SQL part1 and SQL part2 it takes some seconds only but if I run together with group by and without group by it take much time.
SELECT AVG(date_completed-login_date),to_char(to_date(login_date), 'YYYY') as wYear FROM
(
SELECT test.date_completed 'date_completed',sample.login_date 'login_date')
FROM sample test
where (some conditions) ) ---SQL part 1
UNION ALL
[code]...
View 33 Replies
View Related
Jun 3, 2011
I am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
[Code]....
PLAN_TABLE_OUTPUT
Plan hash value: 611392786
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M|
[Code].....
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
View 16 Replies
View Related
Jan 27, 2012
We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read
SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID",
V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1,
V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3,
V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS,
V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE,
V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME,
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN,
V_IDENTIFIER_LOOKUP.IDENTIFIER,
V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE,
[code]...
View 1 Replies
View Related
Mar 31, 2011
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
Is there a way to pass Oracle Hints so that Oracle uses indexes?
View 1 Replies
View Related
Nov 3, 2010
the reason behind the below statements:
1) We cant create TABLE PARTITIONED on CLUSTER or INDEX on CLUSTER TABLE.
2) We cant create a partitioned table with the column of LONG or LONGRAW? (But how it could be possible with BLOB, CLOB?
View 3 Replies
View Related
Sep 18, 2012
I am writing a query and I did get it to work but it shouldn't be this hard, I feel like I am doing something wrong, and there has to be a more elegent solution.
This query works:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
UNION ALL
[Code]...
I feel like I should be able to execute the query like this: This query doesn't work:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
[Code]...
-- not some huge query
View 4 Replies
View Related
May 24, 2010
I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.
So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.
When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.
View 7 Replies
View Related
Jan 18, 2012
I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).
But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!My scale indices and used Selects:
CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
WHERE datum BETWEEN '20120117' AND '20120117'
AND flag_inst = '1' )
AND kontro = '9876521'
[code]...
View 12 Replies
View Related
Jun 2, 2011
We have a person running a query and following is the explain plan
explain plan for
select distinct(extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND')) as ThisBrand
from hh.t_ecomm_mem_relations a
where extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND') not in (select b.code_brand from hh.t_pr_brand b)
and a.code_corr_ecat = 'PREA'
and a.status = 'S'
and a.audit_time > sysdate - 1
;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1904775187
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 32018 | 25 (4)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 14 | 32018 | 25 (4)| 00:00:01 | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 14 | 32018 | 17 (0)| 00:00:01 | KEY | 13 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| T_ECOMM_MEM_RELATIONS | 14 | 32018 | 17 (0)| 00:00:01 | KEY | 13 |
|* 5 | INDEX RANGE SCAN | X_ECOMM_MEM_RELATIONS3 | 15 | | 3 (0)| 00:00:01 | KEY | 13 |
|* 6 | INDEX FULL SCAN | I_PR_BRAND | 1 | 3 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "HH"."T_PR_BRAND" "B" WHERE
LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND'))))
4 - filter("A"."STATUS"='S')
5 - access("A"."AUDIT_TIME">SYSDATE@!-1 AND "A"."CODE_CORR_ECAT"='PREA')
filter("A"."CODE_CORR_ECAT"='PREA')
6 - filter(LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND')))
=========================
I tried not exists and some Antijoin hints in the subquery which is used in filter NOT IN. I tried minus too.
View 15 Replies
View Related
Apr 6, 2011
mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.
PK of mbr : mbr_id
PK of member : mbr_id
other columns are not PK, and have no index on it.
I'm wondering why the statment doesn't use index while ssn and citi_no have index.
MERGE INTO mbr t
USING (SELECT mbr_id,citi_no
FROM member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
UPDATE SET t.asis_mbr_id = a.mbr_id
where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)
View 19 Replies
View Related
Jan 30, 2012
Is there is any view/query from where I can find how many sql using literals.
View 4 Replies
View Related
Mar 25, 2012
I have queries on the execution plan of a sql statement
Following is the example
create table t1 as select s1.nextval id,a.* from dba_objects a;
create table t2 as select s2.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
commit;
create index i1 on t1(id);
create index i2 on t2(id);
create index i11 on t1(object_type);
exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);
select count(*) from t1 where object_type='VIEW';
COUNT(*)
----------
8934
set autotrace traceonly explain
Can we say in the following case, that,
(1) First index on object_type is accessed to get rowids - t1.object_type='VIEW'
(2) Then the filter on owner is applied - t1.owner='SYS'
(3) Then the table T1 is accessed to fetch data from the rowids returned by the index I11 and filer application - TABLE ACCESS BY INDEX ROWID
Though I am unable to understand how filter can be applied to the rowids retrieved from index, we can see from the plan below that The rows accessed have reduced from 8550 to 1221 before we access the table...Thus filter "t1.owner='SYS'" is applied in between. Right?
another question is
Case 1 - do we retrieve a rowid from index for a given value, then retrieve required values from table for that rowid
Thus row at a time in both ... in loop
OR
Case 2 - we first fetch all rowids from index and then retrieve values from table one row at a time from the collection of rowids fetched?
Suppose Case 1 is what is happening then can we say, both the steps mentioned by IDS 2,3 in plan below are executed exactly equal number of times and the filter "t1.owner='SYS'" is applied at some later stage? Of course in this case the values in ROWS stand misleading then
select * from t1,t2 where t1.id = t2.id and t1.object_type='VIEW' and t1.owner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 26873579
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 1 | HASH JOIN | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1221 | 116K| 381 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | I11 | 8550 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 161K| 15M| 533 (1)| 00:00:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."OWNER"='SYS')
3 - access("T1"."OBJECT_TYPE"='VIEW')
View 7 Replies
View Related
Apr 8, 2011
I'm working on a query that will show how many differents SKUs we have on-hand, how many of those SKUs have been cycle-counted, and how many we have yet to cycle-count.I've prepared a sample table and data:
CREATE TABLE SKU
(
ABC VARCHAR2(1 CHAR),
SKU VARCHAR2(32 CHAR) NOT NULL,
Lastcyclecount DATE,
[code]....
What I also want to do is select another column that will group by sku.abc and count the total number of A, B, and C SKUs where the lot.qty is > 0:
SELECT sk.abc AS "STRATA",
COUNT (DISTINCT sk.sku) AS "Total"
FROM sku sk,
(SELECT sku
FROM lot
WHERE qty > 0) item
WHERE item.sku = sk.sku(+)
GROUP BY sk.abc
Finally, I need the last column to display the DIFFERENCE between the two totals from the queries above (the difference between the "counted" and the "total"):
COUNT (DISTINCT sk.sku) - COUNT (DISTINCT s.sku)
View 6 Replies
View Related
Apr 15, 2011
Test1 table have around 385772300 rows. below delete and select statment talking lot of time.
Select stament taking more than 1 hrs.
SELECT TO_NUMBER(MAX(f.T3))
--INTO v_FISCAL_MONTH_ID
FROM Test1 f;
delete statment taking more than 2 hours
DELETE FROM TEST1 WHERE TRUNC(T10) < TRUNC(ADD_MONTHS(SYSDATE,-36));
CREATE TABLE Test1
(
[Code].....
View 4 Replies
View Related
Apr 10, 2012
i am trying to analyze a query i have and noticed that it does not show the sql_id in v$session.
preparing a test case:
create table t1(a number, b varchar(10));
insert into t1 values(123 , 'value1');
when i execute
select count(*) from dual;
select * from dual;
select count(*) from t1;
i can see the sql_id by running
select
sql_id sql_id_,
sql_child_number sql_child_num,
module module_,
action action_,
logon_time lgtime,
[code]....
however, when i'm running
select * from t1
sql_id and sql_child_id in v$session appears to be null, and i can't analyze it.
why those columns are NULL?
View 6 Replies
View Related
Mar 9, 2010
In my code I am using delete statement which is taking too much time to execute.
Statement is as follow:
DELETE FROM TRADE_ORDER_EMP_ALLOCATION T
WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT)
IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT
FROM LOAD_TRADE_ORDER
WHERE IND_IS_BAD_RECORD='N');
Tables Used:
oTRADE_ORDER_EMP_ALLOCATION Row count (329525880)
oLOAD_TRADE_ORDER Row count (29281)
Every column in "IN" clause and select clause is containing index on it
Every time no of rows which to be deleted is vary (May be in hundred ,thousand or hundred thousand )so that I am Unable to use "BITMAP" index on the table "LOAD_TRADE_ORDER" column "IND_IS_BAD_RECORD" though it is containing distinct record in it.
Even table "TRADE_ORDER_EMP_ALLOCATION" is containing "RANGE" PARTITION over it on the column "ARTEMIS_SOURCE_SYSTEM_ID". With this I am enclosing table scripts with Indexes and Partitions over it.
way for fast execution in of above delete statement?
View 4 Replies
View Related
Feb 8, 2011
refer following sql statements and code
Session 1
create table tab1 as select * from dba_objects where object_id is not null;
alter session set events '10046 trace name context forever, level 12';
declare
x number;
begin
for i in 1..4
loop
[code]....
Session 2
after "starting" the above pl/sql block from Session 1, I keep on querying tab2 from Session 2 And as soon as 2 records are inserted in tab2, I create index from Session 2
select * from tab2;
select * from tab2;
select * from tab2;
N
----------
1
2
create index i on tab1(object_id);
As I have tested from a single session (just before this test) such index is used for the sql statement
select count(1) into x from tab1 where object_id=2331;
However when I checked the trace file I am not geeting results as expected
I am expecting 4 execution plans - 2 FTS and 2 Index Access scans and for this I am issuing following command
tkprof dst1_ora_7369.trc dst1_ora_7369.txt aggregate=no sys=no
But unfortunately I am getting following output
SELECT COUNT(1)
FROM
TAB1 WHERE OBJECT_ID=2331
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 4 0.00 0.00 0 2 0 0
[code]....
1) Why I am unable to see 4 execution plans - 2 with FTS and 2 with Index access when I mentioned 'aggregate=no'?
2) Whether the index i will be used for last 2 iterations after first 2 iterations of FTS?
If answer to above question 2) is 'No'
By which method I can force an ongoing sql statement in loop to take different execution path? Of course I can't hard parse sql in 'that' current session Will flushing Shared pool work in above case?
View 6 Replies
View Related
Aug 25, 2010
Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.
View 2 Replies
View Related
Mar 30, 2013
I am going through this scenario:
* 35 | ID TABLE ACCESS BY INDEX ROW | S_ORG_EXT | 3064K| 2472M| | 1 (0)| 00:00:01 |
| 36 | INDEX FULL SCAN | S_ORG_EXT_U1 | 14 | | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
35 - filter("T2"."ACCNT_FLG"<>'N' AND ("T2"."INT_ORG_FLG"<>'Y' OR "T2"."PRTNR_FLG"<>'N'))
This unselective index scan on step 36 of the explain is returning 14 rows but optimizer is selecting 3064 K rows from the table .
I tried creating combined index on all 3 columns mentioned in the predicates for 35th step , but that is not utilized .
how to index this whole expression ::--
(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))
Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;
View 3 Replies
View Related
Dec 6, 2011
I have column containing three values:-N,E,Y.I want to get results with only E and Y values.Is it it possible to create index which would not look for N values.
View 13 Replies
View Related
Jan 27, 2011
I was comparing cost of rebuild vs create index...I carried out the following test
SQL> create table t4 as select * from t1;
Table created.
SQL> create table t5 as select * from t1 where 1=2;
Table created.
SQL> create index i5 on t5(id);
Index created. SQL> select bytes,extents,blocks from user_segments where segment_name='I5';
BYTES EXTENTS BLOCKS
---------- ---------- ----------
65536 1 8
SQL> alter index i5 unusable;
Index altered.
SQL> alter table t5 nologging;
Table altered.
SQL> Alter session set skip_unusable_indexes=True;
Session altered.
SQL> insert /*+ append */ into t5 select * from t1;
563904 rows created.
SQL> commit;
Commit complete.
Now I compared the cost (elapsed time, logical I/O) of the operations
create index i4 on t4(id);
Vs
alter index i5 rebuild online;
Following is the related trace of above 2 steps
create index i4 on t4(id)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 1.17 3.38 9497 7869 335 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.17 3.38 9497 7870 335 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5
[code]....
So which option we shall pick in such cases? {Of course I haven't set 'nologging' for the indices but it is same for both indices we are comparing}
View 2 Replies
View Related
Aug 5, 2010
I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.
View 10 Replies
View Related
Nov 24, 2010
On Oracle 10g, I create, delete and drop a lot of tables. Therefore, the disk is highly fragmented.The execution of a very simple create statement takes more than a minute. If I execute the same statement but first truncate the table and insert the data, it takes less than a second!
I think this has to do with the high fragmentation of the disk. Obviously, I can defragment the disk, but I will always have a high fragmentation since I use a lot of create, delete and drops.
how I can improve the performance of create statements on highly fragmented disks?
View 7 Replies
View Related
Apr 5, 2012
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;
[code]....
View 2 Replies
View Related
Oct 20, 2011
does parallel hint in query can create bottleneck/Slowdown/crash database..??
View 4 Replies
View Related
Aug 10, 2010
which one is better?
unloading 5 tables of same structure using a ETL tool then merging the data
using Union operator to unload 5 tables then do transformations in ETL tool
View 4 Replies
View Related
