Great insight...
Maha Sadanandan, April 11, 2004 - 5:24 pm UTC
thanks for your prompt and thorough response. Your example of simulating online_redef will be very handy little tool.
Followup ques:
The LOB column in the table is NOT defined as NOLOGGING. So will I incur huge redo's? Anything I can do to minimize redo's?
Online redefinition
Bhagat Singh, April 12, 2004 - 12:11 am UTC
Online redefinition funda is really cool could save us from several hours downtime, that was a very good example Tom.
Thanks
CTAS and group by
Allwyn, July 20, 2004 - 8:09 am UTC
Hello sir,
Case 1.
=======
create table dummy as select deptno, count(*) cnt from emp@db817 group by deptno
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
0 REMOTE [DB817.IDC.ORACLE.COM]
SELECT "A1"."DEPTNO",COUNT(*) FROM "EMP" "A1" GROUP BY
"A1"."DEPTNO"
Case 2.
=======
create table test as
select ppc package,
sno srv_num,
count(*) cnt,
sum(AT_CALL_DUR_ROUND_MIN ) bill_min
from rtx.UB@usp
where at_feature_code in ('MTOM','MOBILE','STD','BIRTHD')
and MESSAGE_SWITCH_ID <> 'ICL'
and toll_feature_code is null
group by
ppc,
sno
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
0 SORT (GROUP BY)
0 REMOTE [usp.WORLD]
SELECT "sno","MESSAGE_SWITCH_ID","AT_FEATURE_CODE",
"ppc","AT_CALL_DUR_ROUND_MIN",
"TOLL_FEATURE_CODE" FROM "rtx"."UB" "UB"
WHERE "TOLL_FEATURE_CODE" IS NULL AND
"MESSAGE_SWITCH_ID"<>'ICL' AND ("AT_FEATURE_CODE"='MTOM' OR
"AT_FEATURE_CODE"='MOBILE' OR "AT_FEATURE_CODE"='STD' OR
"AT_FEATURE_CODE"='BIRTHD')
The questions are
1. why in case 2 the SORT (GROUP BY) is not done remotely. Correct me I am interpreting wrong.
2. If I am correct what can be done to make sort happen on remote database.
3. If my interpretation is wrong than why the difference in explain plan between case 1 and case 2.
Thanx in Adv.
July 20, 2004 - 7:49 pm UTC
need more info -- what do the remote tables look like exactly? why different dblinks? no cbo? what versions are we dealing with here? what was use to generate the plans.
CTAS OR INSERT SELECT TAKES EXCESSIVE TIME COMPARED TO STANDALONE SELECT OVER DBLINK
Allwyn, July 20, 2004 - 10:09 pm UTC
My actual problem is CTAS OR INSERT SELECT TAKES EXCESSIVE TIME COMPARED TO STANDALONE SELECT OVER DBLINK.
CTAS
====
create table PRICE_PKG as
select price_plan_code package,
subscriber_no srv_num,
count(*) cnt,
sum(AT_CALL_DUR_ROUND_MIN ) bill_min
from cwusgo.usage_22_B@USG_REP
where at_feature_code in ('MTOM','MOBILE','STD','BIRTHD')
and MESSAGE_SWITCH_ID <> 'ICL'
and toll_feature_code is null
group by
price_plan_code,
subscriber_no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.62 0 2 6 0
Execute 1 23.05 6853.18 0 15 17 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 23.09 6853.80 0 17 23 0
Optimizer goal: CHOOSE
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
0 SORT (GROUP BY)
0 REMOTE [USG_REP.WORLD]
SELECT "SUBSCRIBER_NO","MESSAGE_SWITCH_ID","AT_FEATURE_CODE",
"PRICE_PLAN_CODE","AT_CALL_DUR_ROUND_MIN",
"TOLL_FEATURE_CODE" FROM "CWUSGO"."USAGE_22_B" "USAGE_22_B"
WHERE "TOLL_FEATURE_CODE" IS NULL AND
"MESSAGE_SWITCH_ID"<>'ICL' AND ("AT_FEATURE_CODE"='MTOM' OR
"AT_FEATURE_CODE"='MOBILE' OR "AT_FEATURE_CODE"='STD' OR
"AT_FEATURE_CODE"='BIRTHD')
********************************************************************************
SELECT
=======
select price_plan_code package,
subscriber_no srv_num,
count(*) cnt,
sum(AT_CALL_DUR_ROUND_MIN ) bill_min
from cwusgo.usage_22_B@USG_REP
where at_feature_code in ('MTOM','MOBILE','STD','BIRTHD')
and MESSAGE_SWITCH_ID <> 'ICL'
and toll_feature_code is null
group by
price_plan_code,
subscriber_no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.17 0 2 6 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1934 1.85 441.14 0 0 0 28983
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1936 1.88 441.31 0 2 6 28983
Optimizer goal: CHOOSE
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE (REMOTE)
0 SORT (GROUP BY)
0 TABLE ACCESS (FULL) OF 'USAGE_22_B' [P02AUX]
********************************************************************************
So according to explain plan what I couold make out is while doing CTAS the SORT(GROUP BY) option is not done in remote database.
I carried out a small test in my test database to see the behavior and found in explain plan that its doing sort operation in remote database only.
Please correct my understanding.. And advise on how to tune the CTAS case to make sort happen in Remote database.
********************************************************************************
create table dw as select deptno, count(*) cnt from emp@db817 group by deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 11 0
Execute 1 0.00 0.00 0 61 101 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 61 112 3
Optimizer goal: CHOOSE
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
0 REMOTE [DB817.IDC.ORACLE.COM]
SELECT "A1"."DEPTNO",COUNT(*) FROM "EMP" "A1" GROUP BY
"A1"."DEPTNO"
July 21, 2004 - 7:24 am UTC
guess my exact problem is i asked for specific information and got none of it.
suggestion: either try the driving site hint to push optimization of the subquery to the remote database or use first_rows in the subquery.
A reader, July 25, 2004 - 1:58 pm UTC
Related to original post
Matt, October 19, 2004 - 4:07 am UTC
Win 2K; 9.2.4.0 SE
I am looking for an efficient way to carry out a CTAS with an unpartitioned table that contains a BLOB. The BLOB is typically less than 8192 bytes and the table contains 1.5 million rows. It is inserted, and then selected three or four times in its lifetime. It is never updated.
Currently the CTAS seems to take way to long to complete (over a day with the full data set). I set up a small test case to demonstrate the issue. I would really appreciate it if you can suggest any approaches for speeding up the CTAS.
Thanks, as always.
Mat.
--
-- create the table
--
create table test_t
( docid char(24) not null enable,
partname varchar2(100) not null enable,
mimetype varchar2(100),
contentlength number(*,0),
content blob,
partindex number(*,0),
storagetype varchar2(100),
storageref varchar2(250)
) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
tablespace ebxml_med_data
lob (content)
store as ( tablespace users
disable storage in row
chunk 8192
pctversion 10
nocache
)
/
--
-- Load some representative data (25 minutes to execute)
declare
l_doc blob;
l_doc_len number;
begin
l_doc := utl_raw.cast_to_raw(rpad('FF',7500,'FF'));
l_doc_len := dbms_lob.getlength(l_doc);
insert /*+ append */ into test_t (
docid,
partname,
mimetype,
contentlength,
content,
partindex,
storagetype,
storageref
) select 'abcdefghijklmnopqr'||lpad(to_char(rownum),6,'0')
,decode(mod(rownum,2),0,'ebxml'
,1,'Envelope')
,decode(mod(rownum,2),0,'multipart/related'
,1,'text/xml; charset="UTF-8"')
,l_doc_len
,l_doc
,decode(mod(rownum,2),0,to_char(0)
,1,to_char(1))
,null
,null
from all_objects
where rownum <= 32000;
end;
/
--
-- take half the table with a CTAS - 16 minutes to execute
create table subset_test_t
-- parallel
nologging
tablespace ebxml_med_data
lob (content)
store as ( tablespace ebxml_small_data
disable storage in row
chunk 8192
pctversion 10
nocache )
as select * from test_t where rownum < 16000
/
--
-- delete the remaining rows - 2 minutes to execute
delete from test_t srce
where not exists (select null from subset_test_t trget where srce.docid = trget.docid)
/
October 19, 2004 - 9:22 am UTC
when loading, let it be cached and alter it nocache later.
that way, your CTAS can be filling the buffer cache, while dbwr it writing the data out in the background.
This was on a small desktop machine, in archive log mode, running linux
ops$tkyte@ORA9IR2> create table test_t
2 ( docid char(24) not null enable,
3 partname varchar2(100) not null enable,
4 mimetype varchar2(100),
5 contentlength number(*,0),
6 content blob,
7 partindex number(*,0),
8 storagetype varchar2(100),
9 storageref varchar2(250)
10 ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
11 lob (content)
12 store as ( tablespace users
13 disable storage in row
14 chunk 8192
15 pctversion 10
16 cache
17 )
18 /
Table created.
ops$tkyte@ORA9IR2> declare
2 l_doc blob;
3 l_doc_len number;
4 begin
5 l_doc := utl_raw.cast_to_raw(rpad('FF',7500,'FF'));
6 l_doc_len := dbms_lob.getlength(l_doc);
7 insert /*+ append */ into test_t (
8 docid,
9 partname,
10 mimetype,
11 contentlength,
12 content,
13 partindex,
14 storagetype,
15 storageref
16 ) select 'abcdefghijklmnopqr'||lpad(to_char(rownum),6,'0')
17 ,decode(mod(rownum,2),0,'ebxml'
18 ,1,'Envelope')
19 ,decode(mod(rownum,2),0,'multipart/related'
20 ,1,'text/xml; charset="UTF-8"')
21 ,l_doc_len
22 ,l_doc
23 ,decode(mod(rownum,2),0,to_char(0)
24 ,1,to_char(1))
25 ,null
26 ,null
27 from all_objects
28 where rownum <= &1;
29 end;
30 /
old 28: where rownum <= &1;
new 28: where rownum <= 32000;
PL/SQL procedure successfully completed.
<b>
Elapsed: 00:01:17.07
a far cry from 25 minutes</b>
ops$tkyte@ORA9IR2> create table subset_test_t
2 nologging
3 lob (content)
4 store as ( disable storage in row
5 chunk 8192
6 pctversion 10
7 cache )
8 as select * from test_t where rownum < &1/2
9 /
old 8: as select * from test_t where rownum < &1/2
new 8: as select * from test_t where rownum < 32000/2
Table created.
Elapsed: 00:01:52.57
<b>or 16 minutes</b>
ops$tkyte@ORA9IR2> alter table subset_test_t add constraint subset_test_t_pk primary key(docid);
Table altered.
<b>your example was missing this, with the RBO 99% of your two minutes was spent scaning the table in the subquery</b>
ops$tkyte@ORA9IR2> delete from test_t srce
2 where not exists (select null from subset_test_t trget where srce.docid = trget.docid)
3 /
11932 rows deleted.
Elapsed: 00:00:01.61
ops$tkyte@ORA9IR2>
<b>far from 2 minutes...</b>
with NOCACHE it was:
plsql
Elapsed: 00:02:32.09
ctas
Elapsed: 00:01:25.63
delete
Elapsed: 00:00:01.29
Your mileage will vary.
I get different results...
Matt, October 20, 2004 - 5:01 am UTC
My mileage differs considerably - my CTAS is still running in 17 mins. Can you see anything wrong with the steps below? Any ideas how I can investigate this further?
Please note the text in bold below;
Cheers,
Mat.
SQL>
SQL> alter session set events '10046 trace name context forever, level 12'
2 /
Session altered.
Elapsed: 00:00:00.06
SQL>
SQL> drop table test_t
2 /
Table dropped.
Elapsed: 00:00:04.08
SQL>
SQL> create table test_t
2 ( docid char(24) not null enable,
3 partname varchar2(100) not null enable,
4 mimetype varchar2(100),
5 contentlength number(*,0),
6 content blob,
7 partindex number(*,0),
8 storagetype varchar2(100),
9 storageref varchar2(250)
10 ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
11 lob (content)
12 store as ( tablespace users
13 disable storage in row
14 chunk 8192
15 pctversion 10
16 cache
17 )
18 /
Table created.
Elapsed: 00:00:00.00
SQL>
SQL> declare
2 l_doc blob;
3 l_doc_len number;
4 begin
5 l_doc := utl_raw.cast_to_raw(rpad('FF',7500,'FF'));
6 l_doc_len := dbms_lob.getlength(l_doc);
7 insert /*+ append */ into test_t (
8 docid,
9 partname,
10 mimetype,
11 contentlength,
12 content,
13 partindex,
14 storagetype,
15 storageref
16 ) select 'abcdefghijklmnopqr'||lpad(to_char(rownum),6,'0')
17 ,decode(mod(rownum,2),0,'ebxml'
18 ,1,'Envelope')
19 ,decode(mod(rownum,2),0,'multipart/related'
20 ,1,'text/xml; charset="UTF-8"')
21 ,l_doc_len
22 ,l_doc
23 ,decode(mod(rownum,2),0,to_char(0)
24 ,1,to_char(1))
25 ,null
26 ,null
27 from all_objects
28 where rownum <= &1;
29 end;
30 /
Enter value for 1: 32000
old 28: where rownum <= &1;
new 28: where rownum <= 32000;
PL/SQL procedure successfully completed.
Elapsed: 00:00:27.08
SQL>
SQL> drop table subset_test_t
2 /
Table dropped.
Elapsed: 00:00:00.02
SQL>
SQL> create table subset_test_t
2 nologging
3 lob (content)
4 store as ( disable storage in row
5 chunk 8192
6 pctversion 10
7 cache )
8 as select * from test_t where rownum < &&1/2
9 /
Enter value for 1: 32000
old 8: as select * from test_t where rownum < &&1/2
new 8: as select * from test_t where rownum < 32000/2
Table created.
<b>Elapsed: 00:17:00.02
This still takes too long - 17 minutes! See the end of this post for the tkprof...
</b>
SQL>
SQL> alter table subset_test_t add constraint subset_test_t_pk primary key(docid)
2 /
Table altered.
Elapsed: 00:00:01.01
SQL>
SQL> delete from test_t srce
2 where not exists (select null from subset_test_t trget where srce.docid = trget.docid)
3 /
8135 rows deleted.
Elapsed: 00:00:01.04
SQL>
SQL> spool off
********************************************************************************
create table subset_test_t
nologging
lob (content)
store as ( disable storage in row
chunk 8192
pctversion 10
cache )
as select * from test_t where rownum < 32000/2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 <b>0.90 1021.15</b> 16313 160820 152116 15999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.90 1021.15 16313 160820 152116 15999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 16099 0.11 12.73
control file sequential read 4 0.00 0.00
db file scattered read 19 0.01 0.17
direct path write (lob) 15999 0.10 5.17
log file sync 2 0.00 0.00
direct path write 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.03 0.03
********************************************************************************
<b>The difference in CPU and elapsed times suggests that there should be some waits. However, the waits do not fill the missing time. I monitored the machine (Win2K 4CPU with plenty of RAM) I did not see any paging, only some memory page reads - nothing out of the ordinary.</b>
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.03 0.08 7 2 37 0
Execute 9 7.54 1053.11 16748 452391 464809 24135
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 7.57 1053.20 16755 452393 464846 24135
Misses in library cache during parse: 8
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 88.09 114.09
db file sequential read 16255 0.21 13.42
rdbms ipc reply 231 0.46 0.52
log file sync 6 0.00 0.00
control file sequential read 4 0.00 0.00
direct path write 2 0.00 0.00
db file scattered read 37 0.07 0.41
direct path write (lob) 15999 0.10 5.17
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 757 0.09 0.34 0 1 0 0
Execute 817 5.96 30.73 300 227877 253105 24475
Fetch 723 0.00 0.14 6 1241 0 345
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2297 6.06 31.22 306 229119 253105 24820
Misses in library cache during parse: 30
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 115 0.22 1.06
log file sync 3 0.00 0.02
control file sequential read 4 0.00 0.00
log buffer space 331 1.02 8.35
log file switch completion 4 0.30 0.72
free buffer waits 9 1.03 6.93
direct path write 4 0.02 0.02
db file scattered read 19 0.01 0.11
15 user SQL statements in session.
751 internal SQL statements in session.
766 SQL statements in session.
********************************************************************************
October 20, 2004 - 7:23 am UTC
the nologging is kicking in a direct path write -- let it log, let it log (buffered IO's -- in the background)
how is your CPU there.
Some clarification and more detailed decription if possible please.
Matt, October 20, 2004 - 8:40 am UTC
So you mean run the following instead?
create table subset_test_t
lob (content)
store as ( disable storage in row
chunk 8192
pctversion 10
cache )
as select * from test_t where rownum < 32000/2
I will need to post an update when I get into the office tomorrow.
Can you please explain your comments about nologging kicking off a direct path write? I think I am missing something in my understanding here. Also, this ran much quicker in your test - what is different?
Clearly you see something in the tkprof that I am missing (why isn't the missing time logged?)
The machine CPU dawdled around 2-3%.
Thanks again.
October 20, 2004 - 11:36 am UTC
what type of disks do you have on this system?
i did this on my laptop with single ATA drive. I was 100% cpu pegged during the CTAS. it took about 4minutes for the CTAS.
Response to your previous update...
Matt, October 20, 2004 - 7:02 pm UTC
what type of disks do you have on this system?
>> We have SCSI disks in a RAID 5 configuration - we have no I/O bottlenecks.
i did this on my laptop with single ATA drive. I was 100% cpu pegged during the CTAS. it took about 4minutes for the CTAS.
>> I re-ran the whole test (but used the following for the CTAS):
create table subset_test_t
lob (content)
store as ( disable storage in row
chunk 8192
pctversion 10
cache )
as select * from test_t where rownum < 32000/2
I monitored the machine and during the CTAS all measurements were negligible EXCEPT
"Page Reads/sec". CPU was generally at around 3% with a few minor peaks to 10% (for a second or so). I/O behaved similarly - very low except for a few peaks to around 10%. This is the same behaviour I saw for the nologging CTAS.
The CTAS (with logging) took 17 minutes.
I'm very interested in your response - what do you think might be going on, and how do I prove your hypothesis?
October 20, 2004 - 9:10 pm UTC
"raid 5" + "no io bottlenecks" == VBG
I'll blame this on windows -- but raid 5 for writing (which this is doing alot of) is dog slow (i don't care what your hardware vendor told you, only watching paint dry in winter is slower)
sorry, don't know what tools to run at the OS level on windows -- I'm not very adept at that "OS". All I know is "something is wrong", this is not enough data to really do anything to us.
Do you have "regular" disks you can test on? just to test on -- maybe the local "c" drive?
My response...
Matt, October 21, 2004 - 1:53 am UTC
Yep, I am familiar with the whole RAID 5 debate. However, regardless of the write overhead of RAID 5, I am working on a dedicated DB test box on which there is nothing else going on except this test. Also, as I indicated I have been monitoring the system using performance monitor (perfmon). I really did not see significant reads or writes during the test. Is the RAID 5 overhead so large as to adequately explain the differences we are seeing between your run and mine?
I truly believe that RAID 5 is a red herring in this case. Although I am not discounting this or some other I/O related issue (but remember that other parts of the test that carried out I/O worked fine). I will see what non RAID disks are available and try and prove/disprove the I/O hypothesis...
...here we go.The following demonstrates the same behaviour on the C: drive ( RAID1 ).
SQL>
SQL> connect system/&&system_user_passwd@&&connect_str
Enter value for system_user_passwd: xxxxxx
Enter value for connect_str: xxxxxx
Connected.
SQL>
SQL> create tablespace raid1
2 datafile 'C:\temp\oracle\TSTGW\raid1.dbf' size 1073741888 reuse
3 autoextend on next 1024K maxsize unlimited
4 logging online permanent
5 extent management local uniform size 1024K
6 segment space management auto
7 /
Tablespace created.
Elapsed: 00:00:22.01
SQL>
SQL> create user lob_test
2 identified by lob_test
3 temporary tablespace temp
4 default tablespace raid1
5 /
User created.
Elapsed: 00:00:00.00
SQL>
SQL> grant connect, resource, unlimited tablespace to lob_test
2 /
Grant succeeded.
Elapsed: 00:00:00.00
SQL>
SQL> connect lob_test/lob_test@&&connect_str
Connected.
SQL>
SQL> alter session set events '10046 trace name context forever, level 12'
2 /
Session altered.
Elapsed: 00:00:00.06
SQL>
SQL> drop table test_t
2 /
drop table test_t
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:04.01
SQL>
SQL> create table test_t
2 ( docid char(24) not null enable,
3 partname varchar2(100) not null enable,
4 mimetype varchar2(100),
5 contentlength number(*,0),
6 content blob,
7 partindex number(*,0),
8 storagetype varchar2(100),
9 storageref varchar2(250)
10 ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
11 lob (content)
12 store as ( tablespace raid1
13 disable storage in row
14 chunk 8192
15 pctversion 10
16 cache
17 )
18 /
Table created.
Elapsed: 00:00:00.01
SQL>
SQL> declare
2 l_doc blob;
3 l_doc_len number;
4 begin
5 l_doc := utl_raw.cast_to_raw(rpad('FF',7500,'FF'));
6 l_doc_len := dbms_lob.getlength(l_doc);
7 insert /*+ append */ into test_t (
8 docid,
9 partname,
10 mimetype,
11 contentlength,
12 content,
13 partindex,
14 storagetype,
15 storageref
16 ) select 'abcdefghijklmnopqr'||lpad(to_char(rownum),6,'0')
17 ,decode(mod(rownum,2),0,'ebxml'
18 ,1,'Envelope')
19 ,decode(mod(rownum,2),0,'multipart/related'
20 ,1,'text/xml; charset="UTF-8"')
21 ,l_doc_len
22 ,l_doc
23 ,decode(mod(rownum,2),0,to_char(0)
24 ,1,to_char(1))
25 ,null
26 ,null
27 from all_objects
28 where rownum <= &1;
29 end;
30 /
Enter value for 1: 32000
old 28: where rownum <= &1;
new 28: where rownum <= 32000;
PL/SQL procedure successfully completed.
Elapsed: 00:00:27.00
SQL>
SQL> drop table subset_test_t
2 /
drop table subset_test_t
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
SQL>
SQL> create table subset_test_t
2 nologging
3 lob (content)
4 store as ( disable storage in row
5 chunk 8192
6 pctversion 10
7 cache )
8 as select * from test_t where rownum < &&1/2
9 /
Enter value for 1: 32000
old 8: as select * from test_t where rownum < &&1/2
new 8: as select * from test_t where rownum < 32000/2
Table created.
Elapsed: 00:16:52.06
SQL>
SQL> alter table subset_test_t add constraint subset_test_t_pk primary key(docid)
2 /
Table altered.
Elapsed: 00:00:00.09
SQL>
SQL> delete from test_t srce
2 where not exists (select null from subset_test_t trget where srce.docid = trget.docid)
3 /
5446 rows deleted.
Elapsed: 00:00:00.09
SQL>
SQL> spool off
Any other thoughts?
October 21, 2004 - 6:51 am UTC
could well be. On a raid five system once (this is dated) a create tablespace took 25 minutes with raid 5 on, 30-60 seconds without. just a create tablespace (write lots of zeros). we ended up disabling the parity computation -- create tablespace and then rebuilt the parity in the background after the fact.
don't know what to tell you. tell you what -- enable the 10046 level 12 trace right before the CTAS, exit sqlplus right after, compress the trace file and send it to me using email.
An update and an email...
Matt, October 21, 2004 - 9:16 pm UTC
That is interesting. I'd never realised that the difference could be so great.
In this case, my feeling is that this issue may be something different. We have been using this machine configuration for some time and have not experienced anything like you describe. When the system went live, our migration process carried out many CTAS with much larger data set ('000s rows) and more complex computations in similar times (25 minutes or so). These CTAS did not contain any LOB. This makes me think that it is either:
a) An issue directly related to the configuration of the LOBs in the table.
b) Another DB configuration issue that is not apparent from the 10046 trace (there appears to be significant unnaccounted for time in the trace)
I've re-run the test as you suggest and have sent you a zip of all the data. I'm really interested in any insights you can provide.
cheers.
October 22, 2004 - 6:02 pm UTC
I'm taking this offline with Matt, if we find anything interesting, we'll follow up later.....
Interesting discussion
A reader, October 22, 2004 - 9:05 pm UTC
Tom, please let us know what you found. I'm very interested in this one.
Moving tables using MV (Urgent please!!)
Vicky, November 25, 2004 - 12:23 am UTC
Hi Tom,
The idea of using MV for moving tables is fantastic. I used it in test envirounment and it worked fine. I would like to know following about MV
(1)Is it approate to use MV approach for moving table which contains more than 20 million rows?
(2)Does MV generate redo? if so can we use no logging option to avoid redo entries? (Could you please explain with syntax?)
(3) What will be the impact of MV approach on rollback segment?
(4)Are you familiar with any bugs using this approach?
I am on 8.1.7 (SUN 8 OS)
It would be great if you could answer me as soon as possible.
Thanks in advance.
Regards,
Vicky
November 25, 2004 - 9:34 am UTC
1) The technique outlined here simply mimicks what dbms_redefinition does in 9i. It is as "appropriate" for moving 20 million row as it is for moving 200 rows. It is a technique, you can see exactly what it is doing. If you have 9i, use dbms_redefinition -- before that, you can emulate its behaviour.
2) pretty much, you can minimize that since you build the MV on a prebuild table so the initial copy doesn't have to generate redo (you control that 100%) but after that -- the refreshes must generate redo.
3) it is just *sql* here. you do the initial copy (create table as select or insert /*+ APPEND */ ) -- no undo for that. undo after that is a function of the amount of change that must be applied.
4) i always try to list out any caveats or issues I am aware of when writing. You have tested this technique yourself, that should give you a certain level of confidence.
MV specifics
Russell, November 26, 2004 - 3:14 am UTC
Hi Tom,
Thankyou for suggesting the MV solution. It's a great solution to a problem that has been bugging us for a while.
We have an 8.1.7 Solaris 8 system (w/out partitioning *&%&). The system is extremely IO bound and there is no opportunity to address the root cause in the short term.
The problem table has 200M records and is often the target of full table scans. block size = 8k, row size = 80b => approx 100 rows per block. 90%free 40%used.
The data has accumulated over 5 years. 90% of the records over 2 years old are not required by the users and have been deleted as we need the space. In addition, I believe the remaining records (10%) are uniformly distributed. i.e. approx 10 records/block leaving these 'old' blocks approx 90% free.
With a lot of table scans, I believe this results in far more IO than is necessary. Availability requirements have limited our options for 'compacting' this data. When I read your MV strategy, it was a Eureka moment! I believe that this method will dramatically reduce the number of blocks required for the table, lowering the HWM, increasing performance of the long ops.
Your last post confused me a little with respect to logging during the initial copying of data to the new table.
So, With respect to using MV to 'compact' the records ;
1. Will dbms_mview.refresh generate redo regardless of the chosen method? From my understanding of your info so far, I think the answer to 1. is yes. So;
2. Instead of creating an empty table and using a refresh with method 'C', would you recommend the following approach to avoid logging in the initial table creation?
a. CREATE MATERIALISED VIEW LOG ON EXISTING_TABLE
b. CREATE NEW_TABLE ... NOLOGGING AS SELECT ... FROM EXISTING_TABLE
c. CREATE MATERIALISED VIEW NEW_TABLE ON PREBUILT TABLE
d. exec dbms_mview.refresh('NEW_TABLE',method=> 'F' );
3. If I have misunderstood you, could you please outline a suitable method for minimising logging with this MV approach?
4. I am assuming that the 'F' method of refresh is driven by the MV log created earlier. Is this correct?
Thankyou for your patience and assistance
November 26, 2004 - 9:20 am UTC
1) complete single table refreshes are done with
a) truncate
b) insert /*+ APPEND */
so, if the segment you refresh is set nologging -- the truncate and insert /*+ APPEND */ can be done with no redo generation.
(neat trick: turn on sql_trace, do the refresh -- you can see exactly what SQL Oracle is doing to refresh...)
2) you can do that as well.
3) #1 is probably perfectly OK in your case (you can control the backup of this segment after the fact for media recovery purposes)
4) correct.
MV refresh
Russell Searle, November 26, 2004 - 9:26 pm UTC
Thanks Tom,
Your advice is very helpful
So in this case, it sounds like logging is determined exclusively by the method used to create the table. If it is created with NOLOGGING, then that's what happens
Does this mean that for a table with logging enabled:
CREATE TABLE new_table LOGGING AS SELECT ... FROM old_table;
that "exec dbms_mview.refresh('NEW_TABLE',method=> 'C' )would create logging?
kind Regards
(PS belated happy turkey day!)
November 26, 2004 - 9:48 pm UTC
not used to create, but rather, what is the CURRENT setting of it.
alter table T logging;
alter table T NOlogging;
it is an attribute you can change.
a single table MV refresh (complete) is truncate+insert /*+ append */. if nologging = true - no redo (else redo)
Index on Materialized view
Vicky, February 09, 2005 - 12:18 am UTC
Hi Tom,
Thanks for sharing your knowledge. My question is can we make index on Materialized view? If so could you please let me know how we could make index on Materialized view's column. It would be great if you could show using example.
Once again thanks for your support.
Vicky
February 09, 2005 - 2:49 am UTC
absolutely -- indexes, constraints, whatever.
create materialized view MV .....
create index on mv(a,b,c)
assuming it has columns a,b,c....
Index on MV
Vicky, February 09, 2005 - 7:42 am UTC
Thanks for your quick reply. I am going to move table using MV for that I have to use same name for MV as table
I mean I will create new table T1 for moving T so I have to create materialized view log on T and materialize view on T1 and if I will use following command
DESC T
A number not null
B number
create materialized view log on t;
create materialized view T1
on prebuilt table
refresh fast
as
select * from T;
and then
create index <index_name> on t1(A);
This will create index on Table t1 on column A not on Materialized view T1 column A
Am I right?
Please let mek now if I am wrong
Thanks for your help,
Vicky
February 09, 2005 - 2:26 pm UTC
no idea where "t1" came from -- but yes, that will create an index on a mv named t1 on a column A
CTAS vs Delete - runtime
Murthy, February 10, 2005 - 6:48 pm UTC
Tom: How will it affect if the table is being replicated? Can we take care of it without breaking the replication in circumstances where full refresh is very time consuming.
Thanks
Murthy
February 11, 2005 - 7:50 pm UTC
if you do a create table as select (CTAS), and drop/rename -- well, you lose the replication on the table you dropped.
won't happen that way.
if you are using advanced replication -- you can stop replication for a moment and just run the delete on both copies.
Move replicated tables without breaking replication
Murthy, February 13, 2005 - 3:20 pm UTC
Tom: Thanks a bunch for immediate response. My requirement is slightly different from deleting rows. I submitted a fresh question but submitting here also.
I have huge tables in one tablespace and experiencing waits on datafile. These tables are being replicated. I need to move these tables to different tablespaces. If I use MVs, replication will break. Recreating replication is time consuming and business need does not allow long downtime on replication also. Is it possible to have a methodology like:
a) stop replication refresh job
b) create tables in new tablespaces, create MVs on top of them and refresh
c) drop original tables
d) Drop MVs
e) rename tables created for MVs
steps a to e have been explained by you in this thread earlier
f) Create replication objects on Master in such a way to resemble earlier
replication
g) Make replication FAST refresh job to use the new replication object
Is there any other method? Can you please help me with this problem?
Thanks
Murthy
February 13, 2005 - 4:46 pm UTC
hmm, looks just like a question you just filed -- lets do it there (where I'm recommending you simply move a file if you want to spread the io out)
Online Rebuild with MV won't compile
D(B)A without privileges, March 09, 2005 - 4:41 pm UTC
Tom,
Disclaimer: I'm a contractor DA for a system that essentially has no DBA.
I need to schedule a weekly table cleanup. The table has a LOB. We're on 9iR2 but I can't use the dbms_mview.refresh option because the DBA on our test instance won't give us any of the 'ANY' privileges (sigh). I created an SP that will use the above code to emulate an online rebuild using an MV. It compiles fine but won't run with the basic privileges, of course. I cajoled the DBA to give me the privileges I need: CREATE SNAPSHOT and
EXECUTE on SYS.DBMS_REDEFINITION. Now the SP will not compile. It's not that I get errors, but it just never comes back with any message.
Do you have any ideas?
March 09, 2005 - 6:20 pm UTC
what does the procedure look like?
Online rebuild with MV won't compile
D(B)A without privileges, March 10, 2005 - 1:03 pm UTC
Here's the SP. Thanks for your comments
***********************
CREATE OR REPLACE PROCEDURE SP_DELETE_COMPLTD_TRANSACTION IS
BEGIN
-- Create new table where the 'keeper' data will go
execDDL (
'CREATE TABLE TxAttach(id NUMBER PRIMARY KEY DEFERRABLE , '||
'attachment BLOB) '||
'TABLESPACE DATA '||
'PCTFREE 20 '||
'INITRANS 1 '||
'MAXTRANS 255 '||
'STORAGE ( '||
'INITIAL 16384 '||
'MINEXTENTS 1 '||
'MAXEXTENTS 2147483645 )'||
' NOCACHE ');
--create the MV view on TxAttch. Pull over ONLY the 'keeper' Transactions
execDDL (
'CREATE MATERIALIZED VIEW TxAttach '||
'ON PREBUILT TABLE '||
'REFRESH COMPLETE ON DEMAND '||
'AS SELECT * '||
'FROM TRANSACTION_ATTACHMENT '||
'WHERE id IN ( '||
'SELECT id '||
'FROM TRANSACTION_HISTORY '||
'WHERE STATUS_CODE != ''C'' )');
--refresh mv
DBMS_MVIEW.REFRESH ('TxAttach',method=>'C') ;
-- do grants to new table
execDDL (
'GRANT INSERT, SELECT, UPDATE, DELETE ON TxAttach TO CEAS_ROLE');
--refresh mv
DBMS_MVIEW.REFRESH ('TxAttach',method=>'C');
-- revoke everything on old table from ceas_role -- This won't work because the AppServer uses the
-- CEASAPP user, who owns the table, so you can't revoke access from yourself
-- Ya goota love those DBAs!
-- This is how we'll make it unavailable - the brute force way! Without a net!
execDDL (
'DROP TABLE TRANSACTION_ATTACHMENT');
execDDL (
'DROP MATERIALIZED VIEW TxAttach');
execDDL (
'RENAME TxAttach TO TRANSACTION_ATTACHMENT');
-- open it back up
execDDL (
'GRANT INSERT, SELECT, UPDATE, DELETE ON TRANSACTION_ATTACHMENT TO CEAS_ROLE');
END;
/
**************
March 10, 2005 - 7:20 pm UTC
and what is failing precisely, what won't compile? If have a procedure execDDL and you have execute on dbms_mview (not via a role), looks dandy to me.
Create table as select talking long time.
Rao, November 16, 2006 - 3:55 pm UTC
Tom,
I am trying to USe CTAS for the loading of a table, but the thing is that I want to select the selected columns from the source table not all the columns , and then there is a transformation process which I am doing by calling pl/sql function within the Creat table as select statement.
For about 100,000 rec process is talking 20 mins VS when I select all the column by using Create table nologging as select * table_name , it is talking only one sec.
here is my sql
*****
create table FLAT_USER_REC_5 nologging as select
'mlb' partner_id ,
1 namespace_id ,
createtimestamp created_date,
modifytimestamp modified_date ,
USER_PK_SEQ.NEXTVAL USER_PK ,
USER_ID_SEQ.nextval user_id,
--decode(status,'Y','Y',NULL,'Y','N') active_sw,
modifytimestamp USER_DATA_MODIFIED_DATE,
EMAIL_PK_SEQ.NEXTVAL EMAIL_PK,
--GET_EMAIL_ID_1 Email_id,
userid Email,
--decode(emailtype,'text%',2,'html%',3,1) Email_Format_ID,
PASSWORD_PK_SEQ.NEXTVAL PASSWORD_PK,
--GET_PASSWORD_ID_1 password_id,
1 password_type_id,
ltrim(userpassword,'{SHA}') password,
identity_point_pk_seq.nextval identity_point_pk,
5 identity_point_type_id,
uidnumber identity_point_id,
PROFILE_PK_SEQ.NEXTVAL Gndr_PROFILE_PK,
63 Gndr_attribute_id ,
Gender Gndr_attribute_value,
PROFILE_PK_SEQ.NEXTVAL FN_PROFILE_PK,
14 FN_attribute_id ,
givenname FN_attribute_value,
PROFILE_PK_SEQ.NEXTVAL LN_PROFILE_PK,
15 LN_attribute_id ,
sn LN_attribute_value,
PROFILE_PK_SEQ.NEXTVAL Src_PROFILE_PK,
70 Src_attribute_id,
source Src_attribute_value,
PROFILE_PK_SEQ.NEXTVAL PCA_PROFILE_PK,
67 PCA_attribute_id,
PrefClubAffiliation PCA_attribute_value,
PROFILE_PK_SEQ.NEXTVAL BD_PROFILE_PK,
60 BD_attribute_id,
birthday BD_attribute_value,
PROFILE_PK_SEQ.NEXTVAL BM_PROFILE_PK,
61 BM_attribute_id,
birthmonth BM_attribute_value ,
PROFILE_PK_SEQ.NEXTVAL BY_PROFILE_PK,
62 BY_attribute_id,
birthyear BY_attribute_value,
ADDRESS_PK_SEQ.NEXTVAL ADDRESS_PK,
GET_Address_ID_1 Address_ID,
'Null' Adr_Name,
givenname Adr_First_Name ,
'Null' Adr_Middle_Name,
sn Adr_Last_Name,
BillingAddr1 Adr_Line1 ,
BillingAddr2 Adr_Line2,
'Null' Adr_Line3 ,
BillingCity Adr_City,
BillingState Adr_State,
MIGRATE_MLB_REG_TEST_pkg.ChoosePostalVsZip(trim(PostalCode),trim(BillingZip)) Adr_Postal_Code,
BillingCountry Adr_Country,
BillingPhone Adr_Phone_Number ,
MIGRATE_MLB_REG_TEST_pkg.GetPhoneTypeID('home') Adr_Phone_Type_ID,
PREFERENCE_PK_SEQ.NEXTVAL PREFERENCE_PK,
Vanity_Email_ID_1_SEQ.nextval VanityEmail_PK,
VanityEmailName VanityEmailName,
VanityEmailPasswd VanityEmailPassword,
VanityEmailDomain VanityEmailDomain,
VanityEmailFlag VanityEmailFlag
from
bam_id_owner.TEMP_REGISTERED_CUST_MASTER where rownum < 100000;
********
I don't understand is why the
create table as select , column1, column2.....
Fucntion_call(Column3) from table_name where rownum < 100000;
is talking a lot more.
Please advise.
Thanks
November 16, 2006 - 4:07 pm UTC
I would suspect your function, how about you?
Create table as Select talking long time
Rao, November 16, 2006 - 4:29 pm UTC
Tom,
Thanks for the prompt response!
My Functions should not be creating any problem because, I have tried tried this
select count(1) from(My SQL with functions).
and this is comming back in a sec as well.
here it is
select count(1) from (
select
MIGRATE_MLB_REG_TEST_pkg.GetPartner_ID(trim('mlb'),1) partner_id ,
1 namespace_id ,
createtimestamp created_date,
modifytimestamp modified_date ,
Get_USER_PK_SEQ USER_PK ,
to_char(Get_USER_ID_SEQ) user_id,
decode(status,'Y','Y',NULL,'Y','N') active_sw,
modifytimestamp USER_DATA_MODIFIED_DATE,
Get_EMAIL_PK_SEQ EMAIL_PK,
GET_EMAIL_ID_1 Email_id,
trim(userid) Email,
decode(emailtype,'text%',2,'html%',3,1) Email_Format_ID,
Get_PASSWORD_PK_SEQ PASSWORD_PK,
GET_PASSWORD_ID_1 password_id,
1 password_type_id,
ltrim(userpassword,'{SHA}') password,
Get_identity_point_pk_seq identity_point_pk,
5 identity_point_type_id,
to_char(uidnumber) identity_point_id,
Get_PROFILE_PK_SEQ Gndr_PROFILE_PK,
63 Gndr_attribute_id ,
trim(Gender) Gndr_attribute_value,
Get_PROFILE_PK_SEQ FN_PROFILE_PK,
14 FN_attribute_id ,
trim(givenname) FN_attribute_value,
Get_PROFILE_PK_SEQ LN_PROFILE_PK,
15 LN_attribute_id ,
trim(sn) LN_attribute_value,
Get_PROFILE_PK_SEQ Src_PROFILE_PK,
70 Src_attribute_id,
trim(source) Src_attribute_value,
Get_PROFILE_PK_SEQ PCA_PROFILE_PK,
67 PCA_attribute_id,
trim(PrefClubAffiliation) PCA_attribute_value,
Get_PROFILE_PK_SEQ BD_PROFILE_PK,
60 BD_attribute_id,
trim(birthday) BD_attribute_value,
Get_PROFILE_PK_SEQ BM_PROFILE_PK,
61 BM_attribute_id,
trim(birthmonth) BM_attribute_value ,
Get_PROFILE_PK_SEQ BY_PROFILE_PK,
62 BY_attribute_id,
trim(birthyear) BY_attribute_value,
Get_ADDRESS_PK_SEQ ADDRESS_PK,
GET_Address_ID_1 Address_ID,
'Null' Adr_Name,
trim(givenname) Adr_First_Name ,
'Null' Adr_Middle_Name,
trim(sn) Adr_Last_Name,
trim(BillingAddr1) Adr_Line1 ,
trim(BillingAddr2) Adr_Line2,
'Null' Adr_Line3 ,
trim(BillingCity) Adr_City,
trim(BillingState) Adr_State,
MIGRATE_MLB_REG_TEST_pkg.ChoosePostalVsZip(trim(PostalCode),trim(BillingZip)) Adr_Postal_Code,
trim(BillingCountry) Adr_Country,
trim(BillingPhone) Adr_Phone_Number ,
MIGRATE_MLB_REG_TEST_pkg.GetPhoneTypeID('home') Adr_Phone_Type_ID,
Get_PREFERENCE_PK_SEQ PREFERENCE_PK,
Get_Vanity_Email_ID_1_SEQ VanityEmail_PK,
trim(VanityEmailName) VanityEmailName,
trim(VanityEmailPasswd) VanityEmailPassword,
trim(VanityEmailDomain) VanityEmailDomain,
trim(VanityEmailFlag) VanityEmailFlag
from
bam_id_owner.TEMP_REGISTERED_CUST_MASTER where rownum < 100000);
Thanks
Rao
November 16, 2006 - 4:48 pm UTC
guess what
select count(1)
from (query)
well, that is not at all like
select * from (query)
we can sort of SKIP A LOT OF STUFF, you know, like calling your function - it just isn't relevant.
Never never never never use
select count(*)
from (query)
to "test" the runtime of "query" - it can never be compared, they are not even remotely SIMILAR
Create table as select is taking long
Rao, November 17, 2006 - 5:30 pm UTC
Tom,
thanks for clearing that out. Main Problem was with the getting nextval from the sequences, after caching the sequences , process is running pretty good.
Regards
Rao
Create table as Select is taking long
Rao, November 17, 2006 - 5:33 pm UTC
Tom ,
As you have mentioned that select count(1) from (query) is not good way of determining the query time, then what is the best way to determining/estimating a query time, stated that we dont want oracle to throw results to client program.
Regards
Rao
even more rows to delete ?
Jochen, July 12, 2007 - 12:11 pm UTC
Hi Tom,
as i'm on 9i c) looks pretty nice.
"c) upgrade to 9i and do an online redef AFTER deleting the unwanted data"
But imagine there are about 18 million rows and 16 million are going to be deleted. I fear the delete would run forever.
Is there a d) ?
July 12, 2007 - 1:04 pm UTC
puzzled
Ajeet, January 23, 2009 - 9:12 am UTC
Hi Tom
Matt has mentioned a performance problem with CTAS in his posts 21st 2004. You took that problem offline with him as per your post.
I am facing the similar problem.
consider this :
when I ran this select statement it gets completed in 12 mins.
SQL> set timing on
SQL> alter session set statistics_level = 'ALL' ;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set tracefile_identifier = 'tp2' ;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set events '10046 trace name context forever, level 12';
set timing on ;
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> select count(*)
2 from
(SELECT /*+ parallel(item,4) parallel(ps,4) */
3 4 item.segment1 Item,
item.organization_id,
5 6 item.inventory_item_id,
7 item.costing_enabled_flag,
8 item.inventory_item_status_code,
item.planning_make_buy_code
from mtl_system_items_b item,tran_temp ps , tran_org org
9 where
item.segment1 = ps.itm_item_num_pk
10 11 12 and item.organization_id = org.organization_id) ; 13
COUNT(*)
----------
32178126
Elapsed: 00:11:18.98
SQL> SQL> exit
but when i ran this statement as
create table t as
SELECT /*+ parallel(item,4) parallel(ps,4) */
item.segment1 Item,
item.organization_id,
item.inventory_item_id,
item.costing_enabled_flag,
item.inventory_item_status_code,
item.planning_make_buy_code
from mtl_system_items_b item,tran_temp ps , tran_org org
where
item.segment1 = ps.itm_item_num_pk
and item.organization_id = org.organization_id;
it runs for 4 hrs and then also it did not complete , unfortuantely i could not trace it..but i did monitor and it has not completed in 4 hrs .
what could be the reason..i have raid1+0 not raid5.
Thanks
January 23, 2009 - 9:35 am UTC
you CANNOT compare in any way shape or form a count(*) with a select like that.
When you put the count(*) there, we see:
select count(*)
from
(SELECT /*+ parallel(item,4) parallel(ps,4) */ <b>NULL</b>
from mtl_system_items_b item,tran_temp ps , tran_org org
where item.segment1 = ps.itm_item_num_pk
and item.organization_id = org.organization_id) ;
the selected columns are not relevant, we throw them out and we can use indexes instead of tables - skip the tables right off. In any case, we are dealing with tiny data now.
Please - never ever compare that. It just doesn't make sense.
Now, if you
set autotrace traceonly
run your select - NOT the count(*), the select
set autotrace off
what do you see.
WHERE emaulation in DBMS_REDIFINITION
Fabian Wiktorowski, July 24, 2012 - 7:31 am UTC
I've gone through this thread but was not able to find out how to emulate WHERE clasue while using dbms_redefinition. I've trying do it myself with steps like
* create table
* DBMS_REDEFINITION.START_REDEF_TABLE
* drop mview and create with a where clause
I get ORA-12093: invalid interim table "HR"."MV_RUF4"
So... I give up. Any suggestions?
the version of db -- 11.2.0.3
July 30, 2012 - 9:12 am UTC
offline
Fabian Wiktorowski, July 31, 2012 - 1:25 am UTC
in your proposition there is
then - offline:
drop materialized view (table stays)
drop old_table
rename new_table to old_table;
the most cool feature of the rdbms_redefiniton is making all the things online, so this is not exactly "do it yourselft redefinition"
regards,
July 31, 2012 - 12:16 pm UTC
it is precisely what dbms_redefinition does.
when you issue "finish redef" it will
a) do a sync to copy changes from old to new
b) LOCK BOTH TABLES (we go offline!!)
c) do another sync to get any changes made during a and b
d) swap the table names
e) commit (we go back online!!!)
so, in fact, they are identical as far as being "online" is concerned. They both have a teeny tiny bit of offline time at the end.
:-)
Fabian Wiktorowski, August 01, 2012 - 1:13 am UTC
" teeny tiny bit of offline time" sound lovely -- I'm buying that!
Thank you