Skip to main content

Posts

Showing posts from July, 2013

Ways to Tune the Queries in SQL and Type of Indexes

There are many methods to tune up a query. 1) using EXPLAIN_PLAN. 2) using AUTOTRACE and set Timing ON. 3) tkprof 4) Indexes 5) Cost- based Optimisation 6) Analyze command. 7) parallel query option. Types of indexes B-tree Bitmap Hash Index-organized table Reverse key Function-based Partitioned (local and global) Bitmap join indexes

Difference in length between the first biggest and the second biggest name in sql table

to get difference in length between the first biggest and the second biggest name in SQL table like Employees table. You can try following query to get the same. 1. SELECT MAX(DECODE(DR, 1, L, NULL)) - MAX(DECODE(DR, 2, L, NULL)) AS DIFF FROM ( SELECT LENGTH(FIRST_NAME||LAST_NAME) AS L, DENSE_RANK() OVER(ORDER BY LENGTH(FIRST_NAME||LAST_NAME) DESC) AS DR FROM EMPLOYEES ) WHERE DR IN (1, 2) 2.select max(a) - min(a)from ( select distinct(length(FIRST_NAME)) a from EMPLOYEES order by length(FIRST_NAME) desc) where rownum < 3

How to copy dvd files and folder's to your folder using Command Prompt

How to copy dvd files and folder's to your folder using Command Prompt To copy a single file-->copy source destination for example :- copy D:\ts\steps.txt E:\Newfolder To copy multiple files-->copy source1 source2 destination for example :- copy D:\t\test.txt D:\t\avx.txt E:\Newfolder You can also use robocopy command more!.. make like robocopy (source file) (destination file) copy options and file to copy.. Eg: (in dvd in partition D:) robocopy D:\ C:\users\user_name\desktop\newfolder /s * This command will copy everything in D:\ (which is the dvd drive) to your desktop folder named new folder and /s means it will copy all sub directories except empty ones .. then the * means all files in the D:\ partition!...

Right approach to use join for particular scenario in sql

I have a table that looks like this: CREATE TABLE #C (grpType varchar(10), CPTCode varchar(10) NULL, Month int NULL, MTD money NULL, MonthCount int NULL, YTD money NULL, YearCount int NULL, Code varchar(10) NULL) It has the following data in it: grpType CPTCode Month MTD MonthCount YTD YearCount Code Month 76800 5 1321.61 27 6574.54 82 76800 Month 76856 5 246.01 3 380.64 6 76856 Month 76881 5 9778.95 131 50682.59 509 76881 Month 76942 5 22467.33 190 116663.58 674 76942 Then I have another table: CREATE TABLE #Prod (grpType varchar(10), TotalCharges money NULL, TotalUnits float NULL, RVU float NULL, Code varchar(10) NULL, CPTCode varchar(10) NULL) This table has the following data in it: grpType TotalCharges TotalUnits RVU Code CPTCode Month 6100.00 12 0 76800 76800 Month -475.00 -1 0 76880 76880 Mont