AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 2 hours 4 min ago

query execution time in first attempt

Tue, 08/18/2020 - 00:00
Hi Tom, I am using SQL Developer and a particular user/schema for my Oracle Development Work in my project. While optimizing a query - it completes execution in ~60 sec in the 1st attempt where as it takes ~10 sec in subsequent run.Lets, say I am adding some filter conditions and trigger it again, it will still use the old share pool,library cache and hence completing n less than 60 sec. My question is - How can I check for a query how much time it takes in its first attempt ? Can I do it by - alter system flush buffer_cache; ? But, I don't have privilege for this. Is there any other way where I can check the execution time for a query in its first attempt. Many Thanks, Asit

Longest common substring - Ratcliff-Obershelp similarity algorithm

Tue, 08/18/2020 - 00:00
Hi TOM, Is there a way to make my code faster ? I'm trying to find the longest common sub-string (short: lcs) using dynamic programming; in this respect, i'm building a 2D array with x rows and Y columns, where X - length of string1, Y = length of string2. First, i initialize the array with 0; (can this be avoided ?) Then, i start from top-left, one character (from string 1) at a time and each time there's a match with a character from string2, i put +1 in that cell Here's a visual description: https://imgur.com/a/gz8JTms I'm already using PL/SQL native compilation. Following this lcs function, i'm using <b>Ratcliff-Obershelp</b> similarity algorithm and i'm relly stucked at how to build a function iteratively that would use this "lcs" function; any help would be greatly appreciated. Thanks!

Updating and returning a nested table via OUT parameter

Tue, 08/18/2020 - 00:00
I?m trying to create and fill a nested table with a SELECT? INTO, to be returned as an out parameter from a PL/SQL PROCEDURE. I?ve declared the record and table at both the package level and the schema level as follows: In the package: <code> type rec_DT_REWARD_POOL_ROW is record ( "DT_RP_ID" NUMBER(24,0), "DT_RP_PROG_ID" NUMBER(10,0), "DT_RP_TP_ID" VARCHAR2(18), "DT_RP_TYPE_CODE" NUMBER(10,0), "DT_RP_VALUE" FLOAT(126)); type tab_DT_REWARD_POOL is table of rec_DT_REWARD_POOL_ROW; </code> I?ve also declared it at the schema level <code>CREATE OR REPLACE TYPE REC_DT_REWARD_POOL_ROW AS OBJECT ( "DT_RP_ID" NUMBER(24,0), "DT_RP_PROG_ID" NUMBER(10,0), "DT_RP_TP_ID" VARCHAR2(18), "DT_RP_TYPE_CODE" NUMBER(10,0), "DT_RP_VALUE" FLOAT(126)); CREATE OR REPLACE TYPE tab_DT_REWARD_POOL AS TABLE OF REC_DT_REWARD_POOL_ROW;</code> My Procedure (standalone and package) is <code>PROCEDURE REWARDS_DB_PROD.RETRIEVE_REWRADS( TOKEN REWARDS_DB_PROD.DT_REWARD_POOL.DT_RP_TP_ID%TYPE, P_Available_Reward OUT tab_DT_REWARD_POOL, P_MIC_REWARD OUT tab_DT_REWARD_POOL) AS</code> However, when I try to use the following select into: <code> SELECT DT_RP_ID , DT_RP_PROG_ID , DT_RP_TP_ID , DT_RP_TYPE_CODE , DT_RP_VALUE INTO p_mic_reward FROM REWARD_POOL WHERE ??;</code> I?m not returning the whole table. The package declared one gets a PLS-00642 error: Local collection types not allowed in SQL. The schema declared one tells me ORA-00947: Not enough values. Since the record and Object types had their field lists lifted directly from the CREATE TABLE for REWARD_POOL, I don?t understand what?s going on.

Oracle has any feature similar to "Always Encrypted" that is offered by SQL server?

Tue, 08/11/2020 - 00:00
Hello, It would be great if you can help me here. Can you please share if Oracle has any feature similar to the "Always Encrypted" feature offered by SQL server? Link pasted at end has information on "Always Encrypted". I understand that Oracle offers data redaction to mask data. However, my understanding is that users with high authorization can bypass it. Oracle also offers Vault to control data access. However, there still will be Oracle users that can see the data in clear. It would be really helpful if you can share some pointers. Thanks, AB ------------------------------------------------------------------------------------------------------------------------------- Link: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15 Text from this link: Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. By ensuring on-premises database administrators, cloud database operators, or other high-privileged unauthorized users, can't access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to store their data in Azure, and enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Dynamically passing sequence name to get currval

Mon, 08/10/2020 - 00:00
I am trying to get the currval of all the user sequences in the schema. When I run the below sql it gives me invalid sql statement. I am not sure if the below is the right way to achieve it. Please advise. Assumption: The current value of the sequences are already set in the session. <code> set serveroutput on; declare sq number; sqnm varchar2(50); stmt varchar2(1000); cursor sqnc is (select sequence_name from user_sequences); begin for row in sqnc loop sqnm := row.sequence_name; stmt := 'SELECT' || sqnm ||'.currval into' || sq || 'from dual'; execute immediate stmt; dbms_output_put_line(sqnm || ' ' ||sq); end loop; end; </code>

Best practice to delete rows with a CLOB column

Wed, 08/05/2020 - 00:00
Environment: Oracle 12.1.0.2 on Exadata I have a table with 30 columns, one of which is a CLOB, that contains about 26 million rows. I have a purge job (PL/SQL packaged procedure) that DELETEs rows from nine (9) other tables based on a list of IDs from the driver table that contains the CLOB. I save the list of IDs in a global temporary table and use that to delete the associated rows from the other tables that are done in the correct order to maintain all the FK relationships. I am running a test today that has identified about 98,000 IDs to be purged but I have about 5 million that need to be purged to 'catch up' and going forward I'll have about 10,000 per day to purge. The DELETE on the other 9 tables runs very quickly. The table with the CLOB column is taking many times longer than the others, like several hours as opposed to several minutes. I'm guessing there is a better and more efficient way to remove the rows from the table containing the CLOB and to regain the storage used by the deleted CLOB. I'm currently issuing just a 'DELETE FROM <table> WHERE ID IN (SELECT ID FROM <gtt>', where ID is the PK. I would appreciate any suggestions and order of operational steps to accomplish this purge, both for the 'catch up' and the daily run of 10,000. Thanks in advance for all the help!! Much appreciated! -gary

Comparing 2 Nested Table Collection Which have 180 field defined in each collection by passing field name dynamically.

Wed, 08/05/2020 - 00:00
I have 2 tables which of same structure with around 180 columns in each table with one columns as PK. Each table have around 200 k records. I need to compare to tables columns by column and if for that records any difference is found for any of the remaining 179 columns then need to track that column name and the both the old values and new value from that column from both tables. This can be achieve by SQL statement with UNION and group and LEAD functions but as need to compare the for 179 fields the length of the SQL code is very long. So thought of used nested table collection to compare both the tables by bulk collect both the values is 2 different nested tables and iterate them. First loop to iterate using collection count value and second loop using USER_TAB_COLS to iterate based on number of columns in the tables. Is there any possible to pass the field name to the nested loop dynamically ? Below is the sample code for that. <code>SET SERVEROUTPUT ON; DECLARE TYPE TEST1_TYPE IS TABLE OF TEST1%ROWTYPE ; TEST1_TAB TEST1_TYPE; TEST2_TAB TEST1_TYPE; lcCol1 VARCHAR2(3000); lcCol2 VARCHAR2(3000); lQuery VARCHAR2(3000); CURSOR CUR_TAB_COL IS SELECT COLUMN_NAME ,DATA_TYPE FROM USER_TAB_COLS WHERE TABLE_NAME='TEST1' ORDER BY COLUMN_ID; TYPE COL_TYPE IS TABLE OF CUR_TAB_COL%ROWTYPE; COL_TAB COL_TYPE; BEGIN SELECT * BULK COLLECT INTO TEST1_TAB FROM TEST1 ORDER BY ID; SELECT * BULK COLLECT INTO TEST2_TAB FROM TEST2 ORDER BY ID; OPEN CUR_TAB_COL; FETCH CUR_TAB_COL BULK COLLECT INTO COL_TAB; CLOSE CUR_TAB_COL; FOR I IN 1..TEST2_TAB.count LOOP FOR j IN COL_TAB.FIRST..COL_TAB.LAST LOOP lQuery:='SELECT TEST1_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL'; EXECUTE IMMEDIATE lQuery INTO lcCol1; lQuery:='SELECT TEST2_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL'; EXECUTE IMMEDIATE lQuery INTO lcCol2; END LOOP; END LOOP; END; /</code>

Pages