PL/SQL :: Sample Function Returns Many Times Of Expected Value
Nov 30, 2012
The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.
For example, we have thee tables with the following results:
Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000)
Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000)
Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)
The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"
I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?
View 2 Replies
ADVERTISEMENT
Nov 6, 2010
I just think about write a function with gives me a searched string from table.
select col1, col2 from the_table
col1 col2
--------------------------
SMS yes
Melodies no
Java build
...
And I would like to make a function or something like that where input parameter is from col1 and result is col2. For instance:
select the_function('SMS') from dual;
Result: yes
how to write it?
View 13 Replies
View Related
Nov 25, 2011
I have an existing database with several hundred tables and triggers on the tables in the schema S1. Most of the triggers are for storing audit information USER and SYSDATE in the respective columns.
We have built procedures what will accept incoming XML parameters from a BPMS application on the intranet which will have information about which table to update, which columns to update, what values to update and what the O/S user id is. The O/S user ids are mapped to oracle user ids in a specific table. Therefore when we receive the request we can easily figure out the oracle user id. The application always connects to the database using a specified user id S2.
The trouble is, the existing tables have triggers which read :NEW.userid := USER;and whenever a DML is fired from the procedure, the function USER always returns S2, since that is the user id the BPMS application connects to and therefore the audit columns do not capture the correct information as required.
The impact of changing all the existing triggers/procedures will be a mammoth task including regression testing and will certainly not be approved by the project sponsor.
override the value that USER returns? Using session contexts, etc? If so, we can possibly set it at the point of connect and have to do nothing else.
View 18 Replies
View Related
Oct 16, 2013
I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
View 14 Replies
View Related
Aug 3, 2012
I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.
AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3
[code]....
Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.
I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.
1. What is the best to way (design)to pass input parameter for the function.
2. How to generate hierarchy in xml using DBMS_XMLGEN
View 12 Replies
View Related
Jul 6, 2012
oracle version 11gr2.In the below sample data column a,b if there is Two (2) consecutive numbers are transposed in column B from A it should return string 'true'.
with t as
(
select '123456789' a ,'123476581' b from dual
union all
select '123456789' ,'123465789' from dual
union all
select '332211' ,'332121' from dual
union all
[code]....
View 3 Replies
View Related
Jul 13, 2012
On a Oracle 11g R2 I've a table function ( PIPELINED ) returning rows selected from a table.The first time the function is selected, in a session ( I've tried to disconnect and log in again ), it returns no rows.I've tried to log the call using DBMS_OUTPUT and from what I see the select on the table function returns no rows and no output is printed. So I presume Oracle is not calling the function.
The same function on a similar environment ( same db versions, patches and database structure ) works fine. The second environment is a production environment so it has more memory and some other settings enabled.
View 6 Replies
View Related
Jul 26, 2011
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
View 10 Replies
View Related
Apr 24, 2012
I want to debug a function which returns the table of records. When I try to add the parameters and run the debug it gives the error as
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
if there is any way to debug the function which returns table of records.
View 3 Replies
View Related
Mar 14, 2013
Is there an oracle function that returns the current fiscal year in format (2012/2013)?
View 9 Replies
View Related
Jan 17, 2013
I would like a pl/sql function that returns the check digit of a 9-digit number?
Look at
[UR]......
For example number 109409714 (9 digits) should do check digit. I want validation of check digit, & want to return the check digit.
View 8 Replies
View Related
Nov 2, 2008
Just installed Ora 10g Express Edition. It has a sample database. But is there a rather larger database outside that I can download and import into Oracle for some learning reasons?
View 1 Replies
View Related
Sep 1, 2012
I installed my oracle database successfully but dint install sample schemas.Now i want to install all sample schemas but couldnt find them under $Oracle_home/demo/schema.No **_main.sql is available there. sample scripts as am not in a condition to download companion
View 6 Replies
View Related
Jul 8, 2012
I am new to Dynamic SQL..I create a procedure to get any DDL done against sample HR schema as follows.it goes well! Now when i try to test my procedure with some DDL command passing to the procedure i've created..strange! oracle throws an error as in the /*ERROR!!!!*/ block..
I don't understand why i am facing such an error..
/* Product an Version on my machine */
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]...
View 7 Replies
View Related
Apr 26, 2010
I have a query on , how to view the sample data from a very table which is large in size ( more than 10 million ).
I just need to see some sample data from a large table ( to see what kind of data which is application related ).
My question is :
Select *
from Sample_table
where rownum < 10
is this a Good way to view the sample data ?
I have understanidng that the rownum will be assigined to the rows once all the rows are reteived.
So what is the best way to view ?..I am not sure of any condition to put in the intial time of querying.
View 5 Replies
View Related
May 17, 2013
I am new to Apex. how to import one of oracles sample applications. I can see how to import a workspace but not an application. I have already set up a TEST workspace successfully.
View 1 Replies
View Related
Mar 18, 2013
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?
I know difference of two date column is number. So, first i need to know that data type i should use ?
Secondly, how i get the sum of the all stored time ? for example data are
1.30 -- One hour and thirty min.
2.45
3.50
View 20 Replies
View Related
Aug 4, 2010
SQL> select greatest ( 1000,null) from dual;
GREATEST(1000,NULL)
-------------------
SQL> with t as
2 ( select 1 a from dual union all
3 select null from dual)
4 select max(a) from t;
MAX(A)
----------
1
Why greatest returns null ( as the greatest value among the list of values) as the greatest value while max returns 1 in the above case?
View 17 Replies
View Related
Oct 2, 2010
I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.
The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM
UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
The situation here is that:
1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.
My question is: Do both the inner select statements execute 15000 times?
The statement executes within a second and updates 15000 rows. How is this made possible?
View 10 Replies
View Related
Oct 19, 2012
i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00
so in this it should return 360 minutes.
View 5 Replies
View Related
Jan 31, 2013
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
union all
select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
select param_name,param_type,param_value from t1;
desired output:
need output in a row in three different columns
param_value
01-01-1970 31-12-9999 30
I tried below query
SELECT *
FROM (
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
[code]...
and am getting output as
param_type eff_date_param_value disc_date_param_value initial_ignoring_param_value
mbn256_type <null> <null> <null>
View 2 Replies
View Related
Jul 11, 2012
I have schema level export for user SAMPLE1(Default tablespace USERS) on oracle 9.2.0.1 production database. I want to import into another 9i database on another server, so do i nneed to Create SAMPLE1 user and USERS tablespace in new database again.
View 5 Replies
View Related
Mar 9, 2010
How to get rows N times in a result set?
For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.
But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".
View 2 Replies
View Related
Oct 8, 2011
I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.
New Zealand operates Daylight Savings, so twice a year it changes its clocks.
When New Zealand is on standard time it is UTC+12.
When New Zealand is on daylight savings time it is UTC+13.
Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.
I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.
I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.
Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?
View 6 Replies
View Related
Nov 11, 2011
I ran below query and got the indexes to be rebuild:
SELECT (CASE
WHEN b.partitioned = 'NO'
THEN 'alter index '
|| b.owner
|| '.'
|| b.index_name
|| ' rebuild online; '
--|| b.initial_extent
[code]....
Why the fragmented size is not reducing.
View 14 Replies
View Related
Nov 11, 2010
I am having some difficulties with this trigger. It keeps giving me the error "ERROR at line 5: PL/SQL: ORA-00923: FROM keyword not found where expected" when I am not even using a SELECT before the line it says the error is on? Here is the trigger that I am attempting to create.
CREATE OR REPLACE TRIGGER ClassRestraint
BEFORE INSERT ON Enrolled
FOR EACH ROW
DECLARE
numCourses NUMBER :=0;
myException EXCEPTION;
BEGIN
[code]...
I am getting the error on line 5.
View 5 Replies
View Related
Mar 6, 2012
I have writen PL/SQL packages for data loging through pipe lined function for better peformance.The below packages has been compiled sucessfully but during the run time it shows an error
like "ORA-00932: inconsistent datatypes: expected - got -".
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load
AS
PROCEDURE sp_final_load_mkt_hub;
FUNCTION fnc_pipe_tot_lvl_idx_mon_hub
(pi_input_cur IN SYS_REFCURSOR)
RETURN tot_lvl_idx_mon_tt
PIPELINED;
[code]...
SHOW ERRORS
Error:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 33
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 55
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 92
ORA-06512: at line 1
types scripts:
create or replace type tot_lvl_idx_mon_ot as object
(SSIA_INDEX_ID VARCHAR2(60),
start_date date,
CURRENCY VARCHAR2(10),
LEVEL1 NUMBER(31,11),
TYPE VARCHAR2(31) ,
[code].....
View 2 Replies
View Related
Jun 23, 2011
I have this script which should find tablespaces and their size, joined with free bytes. Trying to run this gives me the SQL Error: ORA-00923: FROM keyword not found where expected.
I have two questions:
1. Where should the FROM be?
2. Is there something wrong with the join.
==============================================
set linesize 120
col "TOTAL (KB)" format 99999999999999999
col "FREE (KB)" format 9999999999999999
col TSNAME format a35
col "% FREE" format a10;
SELECT a.tablespace_name TSNAME, sum(a.bytes/1024) "TOTAL (KB)",
Sum(b.bytes/1024) "FREE (KB)"
To_char(round((sum(a.bytes/1024)/sum(a.bytes/1024))*100),2), 'FM99990D999999')
|| ' % ' "% FREE"
FROM dba_data_files a, dba_free_space b
Where a.tablespace_name = b.tablespacename
Group by a. tablespace_name
[/i]
=============================================
I used the script from [URL]
It worked great but I'm not sure how to use the arithmetic functions to show me MB instead of bytes.
View 1 Replies
View Related
Apr 2, 2012
Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
create table plc_dw_dry_run_fic_rfsh (dry_run_fic_id number,ltr_code varchar2(10),
next_cpn_lvl_id1 number,next_intrvl_code varchar2(10), next_intrvl_value varchar2(120));
begin
insert into plc_dw_dry_run_fic_rfsh values (424740,'1','','LTML','48000');
insert into plc_dw_dry_run_fic_rfsh values (424736,'1','','LTML','32000');
insert into plc_dw_dry_run_fic_rfsh values (424738,'1','','LTML','128000');
insert into plc_dw_dry_run_fic_rfsh values (424783,'1','','LTML','96000');
insert into plc_dw_dry_run_fic_rfsh values (424789,'2','','LTML','96000');
insert into plc_dw_dry_run_fic_rfsh values (424750,'1',198,'LTML','10000');
insert into plc_dw_dry_run_fic_rfsh values (424760,'1',199,'LDFM','20000');
insert into plc_dw_dry_run_fic_rfsh values (424770,'1','','LTML','192000');
end;
commit;
Expected output
---------------
DRY_RUN_FIC_ID LTR_CODE NEW_LTR_CODE
424740 '1' '1b'
424736 '1' '1'
424738 '1' '1'
424783 '1' '1a'
424789 '2' '1a'
424750 '1' '1a'
424760 '1' '1a'
424770 '1' '1a'
Rules
---------
1) First next_cpn_lvl_id1 should be considered (next_intrvl_code can be anything), if next_cpn_lvl_id1 is 198 or 199 then new_ltr_code should have 1a.
2) If next_cpn_lvl_id1 is not 198 or 199, then next_intrvl_code should be considered
a) if next_intrvl_code is LTML and mod of next_intrvl_value and 96000 is zero then new_ltr_code should be 1a
b) if next_intrvl_code is LTML and mod of next_intrvl_value and 48000 is zero then new_ltr_code should be 1b
3) If 1 & 2 are not satisfied, ltr_code should be assigned to new_ltr_code.
View 14 Replies
View Related
Jun 29, 2007
The following syntax gives me the error:
ORA-00923: FROM key word not found where expected
if this is so, why use 'from' in TRIM function? Or is my syntax incorrect?
SYNTAX: select trim leading ('0' from (substr(to_char(polref_nbr),9,10))) "TRIM example" from tbl_vg_adhoc;
View 2 Replies
View Related