Home » SQL & PL/SQL » SQL & PL/SQL » Optimization. (10)
Optimization. [message #357956] Fri, 07 November 2008 07:10 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
hi,

I wrote a 3000 lines of script, that counts distinct values for each column, no of null values for each column, no of non-null values for them and so on. For each table a separate table is made that consists of the results. I used cursor to select various tables of the schema. But problem is that it's too slow.

I could optimize it in the case of nulls, once i count the no of not nulls, i could subtract the not_nulls from total value to get nulls.

I did the same, ie. I declared a variable vbl and i used it to store the not null values. And i subtracted vbl from totalcount. To my dismay it took more than 12 hrs and my boss asked me to stop running it. Where as my unoptimized script completed in 5 hrs.

Any suggestions?

Re: Optimization. [message #357957 is a reply to message #357956] Fri, 07 November 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any suggestions?

Use dbms_stats

Regards
Michel
Re: Optimization. [message #357960 is a reply to message #357956] Fri, 07 November 2008 07:16 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
I could also take nulls and not nulls from user_tab_columns but but there is also where clause in the nulls. So it wouldn't work.

how does dbms_stat help in that respect?
Re: Optimization. [message #357961 is a reply to message #357956] Fri, 07 November 2008 07:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try showing use the actual code
Re: Optimization. [message #357963 is a reply to message #357956] Fri, 07 November 2008 07:21 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hi, here is part of the script,

it's just a block, there are a lot of them,

and it runs multiple times ie for multiple tables.

  • Attachment: orfaq.txt
    (Size: 1.59KB, Downloaded 119 times)
Re: Optimization. [message #357967 is a reply to message #357963] Fri, 07 November 2008 07:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd go with Michels solution.
Use DBMS stats to analyze the table, and then look at NUm_ROWS from USER_TABLES, and NUM_DISTINCT and NUM_NULLS from USER_TAB_COLUMNS.

If you want the values to be exact as of the time you analyze the table, make sure that you set the ESTIMATE_PERCENT parameter to 100
Re: Optimization. [message #357994 is a reply to message #357963] Fri, 07 November 2008 10:43 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why do you want to execute it twice when you could do it in one go? Also if you ask me "datasource is not null" condition is not required. How many records contains the keyword MEDICAL in it. You could try creating a ctx index on that column and see if that is any help to you. For more information about ctx index check the oracle reference manual.
select (count(*)/'||total_med||')*100  from '||tblextract||' where datasource is not null and upper(datasource) like ''%MEDICAL%''

(select count(distinct datasource) from '||tblextract||' where upper(datasource) like ''%MEDICAL%'' )


Last but not least why dynamic sql.

Regards

Raj
Previous Topic: Print Sentence Vertically
Next Topic: data split by range
Goto Forum:
  


Current Time: Fri Dec 09 01:45:08 CST 2016

Total time taken to generate the page: 0.08226 seconds