what is the equivalent of Top n Percent in Oracle sql 11g. Here is my requirement:
I have to find stores contributing top 20% of sales: Store Sales PercetageABC200(200/380)*100=52%XYZ100(100/380)*100=26%PQR50(50/380)*100=13%dddd20(20/380)*100=5%rrrr10(10/380)*100=2%
In the above example I have to get only store ABC as this store alone is contributing more than Top 20%If I change the requirement to Top 70% I have to get store's ABC and XYZ.
I've used custom written statistics gathering scripts that by default gather statistics on large tables with a small estimate percentage and FOR ALL COLUMNS SIZE 1. They allow the estimate percentage to be set higher on individual tables and allow me to choose individual columns to have histograms with the maximum number of buckets. The nice thing about this approach was that the statistics ran very efficiently by default and they could be dialed up as needed to tune individual queries. But, with 11g you can set preferences at the table level so that the automatic stats job or even a manual run of gather_table_stats will use your settings. Also, there is some special sampling algorithm associated with auto_sample_size that you miss out on if you manually set the estimate percentage. So, I could change my approach to query tuning and statistics gathering to use AUTO_SAMPLE_SIZE and FOR ALL COLUMNS SIZE AUTO by default and then override these if needed to tune a query or if needed to make the statistics run in an acceptable length of time. I work in an largish company with a team of about 10 DBAs and a bunch of developers and over 100 Oracle databases so we can't really carefully keep track of each system. Having the defaults be less resource intensive saves me the grief of having stats jobs run too long, but it requires intervention when a query needs to be tuned. Also, with my custom scripts I get a lot of hassles from people wanting to know why I'm not using the Oracle default settings so that is a negative. But I've seen the default settings, even on 11.2, run much too long on large tables. Typically our systems start small and then data grows and grows until things start breaking so the auto stats may seem fine at first, but eventually they will start running too long. Also, when the stats jobs run forever or with the automatic jobs don't finish in the window I get complaints about the stats taking too long or not being updated. So, either direction has its pros and cons as far as I can tell.
View 8 Replies View RelatedIn my database,stale_percent is set to 10. and i have table which has partition. i have dropped table partition dropped which has 10% of data. I would like to know whether oracle will consider only insert,update,delete as stale percent or will it include the dropping paritition data also. Because my stats gather is not running. When i include drop partition data it exceed 10% of stale_percent,But excluding dropped partition it is not exceeds 10% of stale.
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --- -------------
sample_DATA_DATA 235825577 0 0 11-NOV-2012 NO 3
test_DATA_DATA 811618472 0 0 11-NOV-2012 NO 12
sample_DATA_DATA SYS_P2665099 3005966 0 0 11-NOV-2012 NO 0
sample_DATA_DATA SYS_P2665119 3873671 0 0 11-NOV-2012 NO 0
[code].....
Is there no format mask for Percent sign. My customer is creating a Computed field and wants to have the percent sign added to the result. (like a dollar sign). I saw some posts about adding jQuery..??? Is this going to be added to the APEX code some day?
Just like the Money format, FML999G999G999G999G990D00, where did FML come from? it translates to a dollar sign. Isn't there something that could translate to a percent sign
990D000PCT 75.328%
I was installing Oracle 10g Client on my PC. But after Specifying Home Details, I was unable to proceed. The installation hangs in the Loading Product Information form.
The Progress bar stops at 80%.
If the temp space left is 0%, i.e. all temp space used up, is it possible to make new DB connection ( can new users still connect to the DB)?
Or re-phrasing the question... How much of temp space (if at all ) is required for a new user to login to DB? Like SORT_AREA_SIZE in PGA. So, as memory sort area is already used ( Temp space is 100% full), can DB make more new connections?
I created manually a database in 10g, after succesfully creating the dB, I created a single user re: LAMS. Now, I noticed that my USERS tablespace is currently at a 99.96% usage:
SQL> @check_space_used.sql
Monday, March 14, 2011 2:46:22 PM SGT
TABLESPACE_NAME TOTALSPACE TOTALBYTES PERCENTUSED
------------------------------ -------------------- -------------------- --------------------
SYSTEM 1073741824 239599616 23
UNDO 268435456 16449536 7
[code]...
i'm facing a problem while i'm inserting millions of record from table to table that undo tablespace reach 100% full and execution aborted. , how can free the undo tablespace ??? many of extendes are offline. will it flush automatically ??? or what i should do
View 4 Replies View RelatedI need to get the output as ú (Lowercase U-acute) using oracle sql.
SQL> select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('<x>')) from dual;
where <x> represents the hexadecimal equivalent for the character 'ú' ,
I am upgrading from 11.2.0.1 to 11.2.0.3 (64 bit) on Windows (64 bit) I planned to install Oracle 11.2.0.3 on new home and i am not sure wheteher i have to install: Oracle Gateway and Oracle Examples on the 11.2.0.3 new home. I am not using any non - oracle so i may not need Oracle Gateway. I am not sure about examples media . Does it have only examples to learn (or)will it have any optional components which we may require (like companion CD in 10g)? What about the ODBC and com components will it be installed with the database media itself? After DB upgradation is it possible to install oracle gateway/examples on the same home?
View 2 Replies View RelatedWhat would be sybase str(approx_numeric [, length [, decimal] ]) equivalent in oracle.
Sybase call :
select str(10000.5555, 6, 3) as Test.
Tset
-------
10001
Is it possible to send again the function of equivalent of DATEADD in sql for plsql??
View 3 Replies View RelatedIs there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?
View 10 Replies View RelatedI've stucked with a query. I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
[code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age
the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode
My requirment is something like below.
My Oracle DB server time is in UTC. which lags by 9:30 mins to IST.how to get tHe IST time (Asia/Kolkotta) time for that UTC timezone? I can not hard code +9:30 to UTC as this difference varies as per Daylight savings every 6 months.
"php 5.3.x + oracle 11g R2 XE 11.2.x.x"...I'm quite new to oracle (2-4 months) and php(2 weeks).I have one query with results that needs to be reused in several parts of my website. I can't seem to find the equivalent of the mysql_data_seek in Oracle. I wanted to reset the cursor/pointer of oci_ fetch ($result) so that I can scroll the result again.So far this are what I have come up:
A. On first fetch put the the results in a php array and call the array later on.
B. Do the query again.
C. Keep on looking for a mysql_data_seek equivalent and fail.
I'm leaning towards option 'A' but I just wanted to consult the experts.
What is the stream's "tag" equivalent in Golden gate ?My tables are already in GG replication, but i want to do few insert in to source which i dont want to replicate to target.
View 1 Replies View RelatedI see month aging buckets in the Oracle Application I am using (Keystone time and billing). I need to do a query in directly in Oracle (Toad front end to Keystone database) using month aging buckets . I use the following in Access that matches the results in Oracle, but I need to work directly in Toad because I want to avoid having to bring over all the dates when I want to summarize by buckets.:
2-4 Months: Sum(IIf(DateDiff("m",[invoice date],forms!Reporting_Standard!txtlastdayofmonth) In (2,3),[Outstanding Gross],0))
I know about getting the difference between two dates and that works fine for day aging buckets, but I need months, which can deal with months that are different sizes.
how to get java nanotime equivalent in oracle
View 2 Replies View RelatedIs there a save exceptions clause or an equivalent for an Insert as select* statement ?
How do I trap the errors in the below statement -
INSERT INTO copy_emp
SELECT * FROM emp;
Is it an all or nothing scenario ?