Performance Tuning :: Remove Duplicates From Table Using Criteria Giving In Statement
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,
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.
I have tried below steps for removing the table fregmentation but for some table i am not getting good result here.
1. It will collect the data which are having more than 100MB fragmentation.
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len /1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100 order by 8 desc;
2. then move the object(table) to the same tablespace.
alter table abc move; alter table bcd move; alter table efg move;
3. also rebuild the dependent objects.
alter index abc_PK rebuild online;
4. Then analyze the table which are having more than 100MB of fragmentation.
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,
and the rest of those are small tables..All the indexes are in place and I have tried with few hints but this query is slow.
WITH REPS AS (SELECT DISTINCT REP_SET.FILTER_TOKEN FROM (SELECT /*+ INDEX (wdsd WEBDATASETDTL_PK_TEAM) */ DISTINCT WDSD.DATA_SETTING_ID, WDSD.FILTER_TOKEN FROM WEB_DATA_SETTING_DETAIL WDSD, [code]....
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
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10)) insert into testingtype_table values (1124,2424,'cbaaa'); insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10)); create or replace type testingtype_tab as table of testingtype_obj;
Code Block declare l_testingtype_tab testingtype_tab := testingtype_tab(); begin select distinct testingtype_obj(ordernumber ,org_id ,company_name) bulk collect into l_testingtype_tab from testingtype_table; end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
SELECT country_name, substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME, substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE, substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as [code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
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]...
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
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)
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);
(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')
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:
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"):
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');
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?
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
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?
I have a question. If i insert some values to a table and then write a create statement. But if the create statement gives me error (eg: table name already exist). And without commiting if i come out the session will the insert commit?
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A set (A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3 from TableB where A.CODE=B.CODE)
Query 2 Update TableA A set (A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3 from TableB where A.CODE=B.CODE) where exists A.code = (select B.code from TableB B where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID /
attached is the explain plan.
in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.
I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.
I am using one script to delete the records from a table, its taking 1hr to delete.
declare cursor c1 is select ownerid,ownertype from nightly_metric_projects ; v1 c1%rowtype; open c1; loop fetch c1 into v1; exit when c1%notfound; DELETE FROM DGT_ITEMEFFORTDATA WHERE OWNERTYPE = c1.OWNERTYPE AND OWNERID = c1.OWNERID; end loop; close c1; commit;
nightly_metric_projects--1200 records DGT_ITEMEFFORTDATA--13200000
I have normal tables with hugh Data and would like to increase the performace by following means:
1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ). 2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace. 3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.
I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.
