Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Accessing Vararray LIMIT in SQL within PL/SQL

Re: Accessing Vararray LIMIT in SQL within PL/SQL

From: sybrandb <sybrandb_at_gmail.com>
Date: 28 Feb 2007 07:10:01 -0800
Message-ID: <1172675401.599196.182990@k78g2000cwa.googlegroups.com>


On Feb 28, 9:54 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> Hi,
>
> I try to reduce redundancy. If you change the line with comment "--
> ERROR:" to refer to the vararray's LIMIT you get ORA-00904:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 22/5 PL/SQL: SQL Statement ignored
> 28/32 PL/SQL: ORA-00904: "COMPRESS_STATS"."IND_COLS"."LIMIT": invalid
> identifier
>
> Using a constant for the max varray length doesn't help, since it cannot
> be used in the declaration of the varray type. I tried also BULK
> COLLECT with LIMIT but this is only allowed for FETCH. I can get it to
> work by using "last_index" but this seems clunky. Is there any other
> way to get Oracle accept the reference to
> "COMPRESS_STATS"."IND_COLS"."LIMIT" in the query?
>
> Any ideas? Thank you!
>
> Kind regards
>
> robert
>
> PS: If somebody wonders what this function does: it generates an SQL
> statement which will output counts of unique occurrences of all
> combinations of leading columns from an index to help in determining
> whether compressing this index is a good idea.
>
> CREATE OR REPLACE FUNCTION COMPRESS_STATS(
> table_name IN VARCHAR2,
> index_name IN VARCHAR2,
> max_columns IN NUMBER DEFAULT NULL,
> owner IN VARCHAR2 DEFAULT USER
> )
> RETURN VARCHAR2
> AS
> -- index_limit CONSTANT NUMBER := 1000;
>
> TYPE column_list IS VARRAY(1000) OF VARCHAR2(100) NOT NULL;
>
> ind_cols column_list;
> sql_code VARCHAR2(4000);
> i1 NUMBER;
> i2 NUMBER;
> last_index NUMBER;
> BEGIN
> -- get all column names of this index
> -- OK: last_index := ind_cols.LIMIT;
>
> SELECT column_name
> BULK COLLECT INTO COMPRESS_STATS.ind_cols
> FROM all_ind_columns
> WHERE index_owner = COMPRESS_STATS.owner
> AND index_name = COMPRESS_STATS.index_name
> AND table_name = COMPRESS_STATS.table_name
> AND column_position <= 1000
> -- ERROR: COMPRESS_STATS.ind_cols.LIMIT
> -- OK: AND column_position <= COMPRESS_STATS.last_index
> ORDER BY index_name ASC, column_position ASC;
>
> -- did we limit the number of columns to output?
> IF max_columns IS NOT NULL AND ind_cols.COUNT > max_columns THEN
> last_index := max_columns;
> ELSE
> last_index := ind_cols.COUNT;
> END IF;
>
> -- create the SQL statement that will yield stats
> -- start with SELECT
> sql_code := 'SELECT COUNT(*) "Records"';
>
> -- add all leading lists of column names
> FOR i1 IN ind_cols.FIRST .. last_index -- ind_cols.LAST
> LOOP
> sql_code := sql_code || ', COUNT(DISTINCT ';
>
> FOR i2 IN ind_cols.FIRST .. i1
> LOOP
> IF i2 > ind_cols.FIRST THEN
> sql_code := sql_code || ' || ''-'' || ';
> END IF;
>
> sql_code := sql_code || '"' || ind_cols(i2) || '"';
> END LOOP;
>
> sql_code := sql_code || ') "COMPRESS ' || i1 || '"';
> END LOOP;
>
> -- add FROM clause
> sql_code := sql_code || ' FROM "' ||
> owner || '"."' || table_name || '"';
>
> RETURN sql_code;
> END COMPRESS_STATS;
Actually
analyze index blah validate structure already calculates this for you, and stores it in
OPT_CMPR_PCTSAVE of INDEX_STATS

--
Sybrand Bakker
Senior Oracle DBA
Received on Wed Feb 28 2007 - 09:10:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US