RE: [sqr-users] Re-Indexing a Table in SQR

["Bob Stone" <bstone@fastenal.com>]

One thing you may want to do is put a 'stop' in your processing - and
analyze the table while it has data.  Once the table knows what it
should look like normally, it will be able to give better info to the
optimizer.

I'm not sure how big your table is, but if it's less than a million rows
then the table not having stats probably isn't your problem.

If it's maybe the smallest table in some of your joins (temporary tables
usually are), you may try an /*+ordered */ hint and put this empty table
as the first one in the from clause.  With no stats it would probably
still do a full scan, but then it can use key-joins or indexed joins
back to the other tables.  Besides, if you're using most/all of the data
from the table you just loaded then you need to do a full scan anyway -
no stats would do that for you.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
Wright, David L. II
Sent: Friday, May 12, 2006 10:44 AM
To: 'This list is for discussion about the SQR database reporting
language from Hyperion Solutions.'
Subject: RE: [sqr-users] Re-Indexing a Table in SQR

Bob,

I am loading an empty table in my SQR. Unfortunately I cannot load it in
key
sequence which is causing sluggish processing times.  I've tried the
ANALYZE
TABLE.  That was successful but didn't improve the run times at all.
I've
also tried DBMS_STATS but it errors out as an invalid SQL statement.
I'm
going to try DROP INDEX and then re-index the table next.  This option
is a
no-no in our environment but I've run out of options at this point.

Dave



-----Original Message-----
From: sqr-users-bounces+david.l.wright.ii=saic.com@sqrug.org
[mailto:sqr-users-bounces+david.l.wright.ii=saic.com@sqrug.org] On
Behalf Of
Bob Buford
Sent: Friday, May 12, 2006 8:31 AM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] Re-Indexing a Table in SQR


David,

Are you dropping the table or deleting from it?  If dropping, your
indices
are dropped as well and creating the table would require creating the
appropriate index as well.

In Oracle (my company's database of choice), up through 9i, Compute
Statistics will update your index.  For 10g on, it is supposed to be
automatically managed.  See here:
http://www.dba-oracle.com/t_index_statistics_create.htm

-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu] 
Sent: Thursday, May 11, 2006 2:15 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] Re-Indexing a Table in SQR


I would drop the index, load the table and then re-index.

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Wright, David L. II
Sent: Thursday, May 11, 2006 12:56 PM
To: 'SQR Users Group'
Subject: [sqr-users] Re-Indexing a Table in SQR

Greetings all,
 
I have a situation where I need to re-index a table in SQR after loading
it.
There is no way to organize the input sequentially so therein lies my
problem.
 
Other than dropping the index and re-indexing (which is a no-no at our
site)
is there any other way to re-index a table inside an SQR?
 
I have already tried to run DBMS_STATS but SQR doesn't like that.
 
Thanks in advance for any help you can offer
 
 
David L. Wright, II
SAIC Sr. Technical Consultant, PeopleSoft

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users