Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Approximate Query Processing

DZone's Guide to

Approximate Query Processing

In this article, I will talk about some of the new SQL functions that come with Oracle 18c.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

In this article, I will talk about some of the new SQL functions that come with Oracle 18c. I hope this article is useful in the sense of awareness. 

As we know, Oracle 18c was announced in February last year, and users are made available for use in Cloud environments. Following this announcement, Oracle has introduced a new version for its end users via the LiveSQL platform. 

I will perform the function tests that are the subject of this article through the LiveSQL platform.

First, we will work on creating a table and enter data into it.

CREATE TABLE employees
(
   employee_id      NUMBER (6),
   first_name       VARCHAR2 (20),
   last_name        VARCHAR2 (25) CONSTRAINT emp_last_name_nn NOT NULL,
   email            VARCHAR2 (25) CONSTRAINT emp_email_nn NOT NULL,
   phone_number     VARCHAR2 (20),
   hire_date        DATE CONSTRAINT emp_hire_date_nn NOT NULL,
   job_id           VARCHAR2 (10) CONSTRAINT emp_job_nn NOT NULL,
   salary           NUMBER (8, 2),
   commission_pct   NUMBER (2, 2),
   manager_id       NUMBER (6),
   department_id    NUMBER (4),
   CONSTRAINT emp_salary_min CHECK (salary > 0),
   CONSTRAINT emp_email_uk UNIQUE (email)
);

INSERT INTO employees
     VALUES (100,'Steven','King','SKING','515.123.4567',TO_DATE ('17-HAZ-1987', 'dd-MON-yyyy'),'AD_PRES',24000,NULL,NULL,90);

INSERT INTO employees
     VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',TO_DATE ('21-SEP-1989', 'dd-MON-yyyy'),'AD_VP',17000,NULL,100,90);

INSERT INTO employees
     VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569',TO_DATE ('13-JAN-1993', 'dd-MON-yyyy'),'AD_VP',17000,NULL,100,90);

INSERT INTO employees
     VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567',TO_DATE ('03-JAN-1990', 'dd-MON-yyyy'),'IT_PROG',9000,NULL,102,60);

INSERT INTO employees
     VALUES (104,'Bruce','Ernst','BERNST','590.423.4568',TO_DATE ('21-MAY-1991', 'dd-MON-yyyy'),'IT_PROG',6000,NULL,103,60);

INSERT INTO employees
     VALUES (105,'David','Austin','DAUSTIN','590.423.4569',TO_DATE ('25-JUN-1997', 'dd-MON-yyyy'),'IT_PROG',4800,NULL,103,60);

INSERT INTO employees
     VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560',TO_DATE ('05-FEB-1998', 'dd-MON-yyyy'),'IT_PROG',4800,NULL,103,60);

INSERT INTO employees
     VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567',TO_DATE ('07-FEB-1999', 'dd-MON-yyyy'),'IT_PROG',4200,NULL,103,60);

INSERT INTO employees
     VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569',TO_DATE ('17-AUG-1994', 'dd-MON-yyyy'),'FI_MGR',12000,NULL,101,100);

INSERT INTO employees
     VALUES (109,'Daniel','Faviet','DFAVIET','515.124.4169',TO_DATE ('16-AUG-1994', 'dd-MON-yyyy'),'FI_ACCOUNT',9000,NULL,108,100);

INSERT INTO employees
     VALUES (110,'John','Chen','JCHEN','515.124.4269',TO_DATE ('28-SEP-1997', 'dd-MON-yyyy'),'FI_ACCOUNT',8200,NULL,108,100);

INSERT INTO employees
     VALUES (111,'Ismael','Sciarra','ISCIARRA','515.124.4369',TO_DATE ('30-SEP-1997', 'dd-MON-yyyy'),'FI_ACCOUNT',7700,NULL,108,100);

INSERT INTO employees
     VALUES (112,'Jose Manuel','Urman','JMURMAN','515.124.4469',TO_DATE ('07-MAR-1998', 'dd-MON-yyyy'),'FI_ACCOUNT',7800,NULL,108,100);

INSERT INTO employees
     VALUES (113,'Luis','Popp','LPOPP','515.124.4567',TO_DATE ('07-DEC-1999', 'dd-MON-yyyy'),'FI_ACCOUNT',6900,NULL,108,100);

INSERT INTO employees
     VALUES (114,'Den','Raphaely','DRAPHEAL','515.127.4561',TO_DATE ('07-DEC-1994', 'dd-MON-yyyy'),'PU_MAN',11000,NULL,100,30);

INSERT INTO employees
     VALUES (115,'Alexander','Khoo','AKHOO','515.127.4562',TO_DATE ('18-MAY-1995', 'dd-MON-yyyy'),'PU_CLERK',3100,NULL,114,30);

INSERT INTO employees
     VALUES (116,'Shelli','Baida','SBAIDA','515.127.4563',TO_DATE ('24-DEC-1997', 'dd-MON-yyyy'),'PU_CLERK',2900,NULL,114,30);

INSERT INTO employees
     VALUES (117,'Sigal','Tobias','STOBIAS','515.127.4564',TO_DATE ('24-JUL-1997', 'dd-MON-yyyy'),'PU_CLERK',2800,NULL,114,30);

INSERT INTO employees
     VALUES (118,'Guy','Himuro','GHIMURO','515.127.4565',TO_DATE ('15-NOV-1998', 'dd-MON-yyyy'),'PU_CLERK',2600,NULL,114,30);

INSERT INTO employees
     VALUES (119,'Karen','Colmenares','KCOLMENA','515.127.4566',TO_DATE ('10-AUG-1999', 'dd-MON-yyyy'),'PU_CLERK',2500,NULL,114,30);

INSERT INTO employees
     VALUES (120,'Matthew','Weiss','MWEISS','650.123.1234',TO_DATE ('18-JUL-1996', 'dd-MON-yyyy'),'ST_MAN',8000,NULL,100,50);

INSERT INTO employees
     VALUES (121,'Adam','Fripp','AFRIPP','650.123.2234',TO_DATE ('10-APR-1997', 'dd-MON-yyyy'),'ST_MAN',8200,NULL,100,50);

INSERT INTO employees
     VALUES (122,'Payam','Kaufling','PKAUFLIN','650.123.3234',TO_DATE ('01-MAY-1995', 'dd-MON-yyyy'),'ST_MAN',7900,NULL,100,50);

INSERT INTO employees
     VALUES (123,'Shanta','Vollman','SVOLLMAN','650.123.4234',TO_DATE ('10-OCT-1997', 'dd-MON-yyyy'),'ST_MAN',6500,NULL,100,50);

INSERT INTO employees
     VALUES (124,'Kevin','Mourgos','KMOURGOS','650.123.5234',TO_DATE ('16-NOV-1999', 'dd-MON-yyyy'),'ST_MAN',5800,NULL,100,50);

INSERT INTO employees
     VALUES (125,'Julia','Nayer','JNAYER','650.124.1214',TO_DATE ('16-JUL-1997', 'dd-MON-yyyy'),'ST_CLERK',3200,NULL,120,50);

INSERT INTO employees
     VALUES (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224',TO_DATE ('28-SEP-1998', 'dd-MON-yyyy'),'ST_CLERK',2700,NULL,120,50);

INSERT INTO employees
     VALUES (127,'James','Landry','JLANDRY','650.124.1334',TO_DATE ('14-JAN-1999', 'dd-MON-yyyy'),'ST_CLERK',2400,NULL,120,50);

INSERT INTO employees
     VALUES (128,'Steven','Markle','SMARKLE','650.124.1434',TO_DATE ('08-MAR-2000', 'dd-MON-yyyy'),'ST_CLERK',2200,NULL,120,50);

INSERT INTO employees
     VALUES (129,'Laura','Bissot','LBISSOT','650.124.5234',TO_DATE ('20-AUG-1997', 'dd-MON-yyyy'),'ST_CLERK',3300,NULL,121,50);

INSERT INTO employees
     VALUES (130,'Mozhe','Atkinson','MATKINSO','650.124.6234',TO_DATE ('30-OCT-1997', 'dd-MON-yyyy'),'ST_CLERK',2800,NULL,121,50);

INSERT INTO employees
     VALUES (131,'James','Marlow','JAMRLOW','650.124.7234',TO_DATE ('16-FEB-1997', 'dd-MON-yyyy'),'ST_CLERK',2500,NULL,121,50);

INSERT INTO employees
     VALUES (132,'TJ','Olson','TJOLSON','650.124.8234',TO_DATE ('10-APR-1999', 'dd-MON-yyyy'),'ST_CLERK',2100,NULL,121,50);

INSERT INTO employees
     VALUES (133,'Jason','Mallin','JMALLIN','650.127.1934',TO_DATE ('14-JUN-1996', 'dd-MON-yyyy'),'ST_CLERK',3300,NULL,122,50);

INSERT INTO employees
     VALUES (134,'Michael','Rogers','MROGERS','650.127.1834',TO_DATE ('26-AUG-1998', 'dd-MON-yyyy'),'ST_CLERK',2900,NULL,122,50);

INSERT INTO employees
     VALUES (135,'Ki','Gee','KGEE','650.127.1734',TO_DATE ('12-DEC-1999', 'dd-MON-yyyy'),'ST_CLERK',2400,NULL,122,50);

INSERT INTO employees
     VALUES (136,'Hazel','Philtanker','HPHILTAN','650.127.1634',TO_DATE ('06-FEB-2000', 'dd-MON-yyyy'),'ST_CLERK',2200,NULL,122,50);

INSERT INTO employees
     VALUES (137,'Renske','Ladwig','RLADWIG','650.121.1234',TO_DATE ('14-JUL-1995', 'dd-MON-yyyy'),'ST_CLERK',3600,NULL,123,50);

INSERT INTO employees
     VALUES (138,'Stephen','Stiles','SSTILES','650.121.2034',TO_DATE ('26-OCT-1997', 'dd-MON-yyyy'),'ST_CLERK',3200,NULL,123,50);

INSERT INTO employees
     VALUES (139,'John','Seo','JSEO','650.121.2019',TO_DATE ('12-FEB-1998', 'dd-MON-yyyy'),'ST_CLERK',2700,NULL,123,50);

INSERT INTO employees
     VALUES (140,'Joshua','Patel','JPATEL','650.121.1834',TO_DATE ('06-APR-1998', 'dd-MON-yyyy'),'ST_CLERK',2500,NULL,123,50);

INSERT INTO employees
     VALUES (141,'Trenna','Rajs','TRAJS','650.121.8009',TO_DATE ('17-OCT-1995', 'dd-MON-yyyy'),'ST_CLERK',3500,NULL,124,50);

INSERT INTO employees
     VALUES (142,'Curtis','Davies','CDAVIES','650.121.2994',TO_DATE ('29-JAN-1997', 'dd-MON-yyyy'),'ST_CLERK',3100,NULL,124,50);

INSERT INTO employees
     VALUES (143,'Randall','Matos','RMATOS','650.121.2874',TO_DATE ('15-MAR-1998', 'dd-MON-yyyy'),'ST_CLERK',2600,NULL,124,50);

INSERT INTO employees
     VALUES (144,'Peter','Vargas','PVARGAS','650.121.2004',TO_DATE ('09-JUL-1998', 'dd-MON-yyyy'),'ST_CLERK',2500,NULL,124,50);

INSERT INTO employees
     VALUES (145,'John','Russell','JRUSSEL','011.44.1344.429268',TO_DATE ('01-OCT-1996', 'dd-MON-yyyy'),'SA_MAN',14000,.4,100,80);

INSERT INTO employees
     VALUES (146,'Karen','Partners','KPARTNER','011.44.1344.467268',TO_DATE ('05-JAN-1997', 'dd-MON-yyyy'),'SA_MAN',13500,.3,100,80);

INSERT INTO employees
     VALUES (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278',TO_DATE ('10-MAR-1997', 'dd-MON-yyyy'),'SA_MAN',12000,.3,100,80);

INSERT INTO employees
     VALUES (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268',TO_DATE ('15-OCT-1999', 'dd-MON-yyyy'),'SA_MAN',11000,.3,100,80);

INSERT INTO employees
     VALUES (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',TO_DATE ('29-JAN-2000', 'dd-MON-yyyy'),'SA_MAN',10500,.2,100,80);

INSERT INTO employees
     VALUES (150,'Peter','Tucker','PTUCKER','011.44.1344.129268',TO_DATE ('30-JAN-1997', 'dd-MON-yyyy'),'SA_REP',10000,.3,145,80);

INSERT INTO employees
     VALUES (151,'David','Bernstein','DBERNSTE','011.44.1344.345268',TO_DATE ('24-MAR-1997', 'dd-MON-yyyy'),'SA_REP',9500,.25,145,80);

INSERT INTO employees
     VALUES (152,'Peter','Hall','PHALL','011.44.1344.478968',TO_DATE ('20-AUG-1997', 'dd-MON-yyyy'),'SA_REP',9000,.25,145,80);

INSERT INTO employees
     VALUES (153,'Christopher','Olsen','COLSEN','011.44.1344.498718',TO_DATE ('30-MAR-1998', 'dd-MON-yyyy'),'SA_REP',8000,.2,145,80);

INSERT INTO employees
     VALUES (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',TO_DATE ('09-DEC-1998', 'dd-MON-yyyy'),'SA_REP',7500,.2,145,80);

INSERT INTO employees
     VALUES (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508',TO_DATE ('23-NOV-1999', 'dd-MON-yyyy'),'SA_REP',7000,.15,145,80);

INSERT INTO employees
     VALUES (156,'Janette','King','JKING','011.44.1345.429268',TO_DATE ('30-JAN-1996', 'dd-MON-yyyy'),'SA_REP',10000,.35,146,80);

INSERT INTO employees
     VALUES (157,'Patrick','Sully','PSULLY','011.44.1345.929268',TO_DATE ('04-MAR-1996', 'dd-MON-yyyy'),'SA_REP',9500,.35,146,80);

INSERT INTO employees
     VALUES (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268',TO_DATE ('01-AUG-1996', 'dd-MON-yyyy'),'SA_REP',9000,.35,146,80);

INSERT INTO employees
     VALUES (159,'Lindsey','Smith','LSMITH','011.44.1345.729268',TO_DATE ('10-MAR-1997', 'dd-MON-yyyy'),'SA_REP',8000,.3,146,80);

INSERT INTO employees
     VALUES (160,'Louise','Doran','LDORAN','011.44.1345.629268',TO_DATE ('15-DEC-1997', 'dd-MON-yyyy'),'SA_REP',7500,.3,146,80);

INSERT INTO employees
     VALUES (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268',TO_DATE ('03-NOV-1998', 'dd-MON-yyyy'),'SA_REP',7000,.25,146,80);

INSERT INTO employees
     VALUES (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268',TO_DATE ('11-NOV-1997', 'dd-MON-yyyy'),'SA_REP',10500,.25,147,80);

INSERT INTO employees
     VALUES (163,'Danielle','Greene','DGREENE','011.44.1346.229268',TO_DATE ('19-MAR-1999', 'dd-MON-yyyy'),'SA_REP',9500,.15,147,80);

INSERT INTO employees
     VALUES (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268',TO_DATE ('24-JAN-2000', 'dd-MON-yyyy'),'SA_REP',7200,.10,147,80);

INSERT INTO employees
     VALUES (165,'David','Lee','DLEE','011.44.1346.529268',TO_DATE ('23-FEB-2000', 'dd-MON-yyyy'),'SA_REP',6800,.1,147,80);

INSERT INTO employees
     VALUES (166,'Sundar','Ande','SANDE','011.44.1346.629268',TO_DATE ('24-MAR-2000', 'dd-MON-yyyy'),'SA_REP',6400,.10,147,80);

INSERT INTO employees
     VALUES (167,'Amit','Banda','ABANDA','011.44.1346.729268',TO_DATE ('21-APR-2000', 'dd-MON-yyyy'),'SA_REP',6200,.10,147,80);

INSERT INTO employees
     VALUES (168,'Lisa','Ozer','LOZER','011.44.1343.929268',TO_DATE ('11-MAR-1997', 'dd-MON-yyyy'),'SA_REP',11500,.25,148,80);

INSERT INTO employees
     VALUES (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268',TO_DATE ('23-MAR-1998', 'dd-MON-yyyy'),'SA_REP',10000,.20,148,80);

INSERT INTO employees
     VALUES (170,'Tayler','Fox','TFOX','011.44.1343.729268',TO_DATE ('24-JAN-1998', 'dd-MON-yyyy'),'SA_REP',9600,.20,148,80);

INSERT INTO employees
     VALUES (171,'William','Smith','WSMITH','011.44.1343.629268',TO_DATE ('23-FEB-1999', 'dd-MON-yyyy'),'SA_REP',7400,.15,148,80);

INSERT INTO employees
     VALUES (172,'Elizabeth','Bates','EBATES','011.44.1343.529268',TO_DATE ('24-MAR-1999', 'dd-MON-yyyy'),'SA_REP',7300,.15,148,80);

INSERT INTO employees
     VALUES (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268',TO_DATE ('21-APR-2000', 'dd-MON-yyyy'),'SA_REP',6100,.10,148,80);

INSERT INTO employees
     VALUES (174,'Ellen','Abel','EABEL','011.44.1644.429267',TO_DATE ('11-MAY-1996', 'dd-MON-yyyy'),'SA_REP',11000,.30,149,80);

INSERT INTO employees
     VALUES (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266',TO_DATE ('19-MAR-1997', 'dd-MON-yyyy'),'SA_REP',8800,.25,149,80);

INSERT INTO employees
     VALUES (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',TO_DATE ('24-MAR-1998', 'dd-MON-yyyy'),'SA_REP',8600,.20,149,80);

INSERT INTO employees
     VALUES (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',TO_DATE ('23-APR-1998', 'dd-MON-yyyy'),'SA_REP',8400,.20,149,80);

INSERT INTO employees
     VALUES (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',TO_DATE ('24-MAY-1999', 'dd-MON-yyyy'),'SA_REP',7000,.15,149,NULL);

INSERT INTO employees
     VALUES (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',TO_DATE ('04-JAN-2000', 'dd-MON-yyyy'),'SA_REP',6200,.10,149,80);

INSERT INTO employees
     VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876',TO_DATE ('24-JAN-1998', 'dd-MON-yyyy'),'SH_CLERK',3200,NULL,120,50);

INSERT INTO employees
     VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877',TO_DATE ('23-FEB-1998', 'dd-MON-yyyy'),'SH_CLERK',3100,NULL,120,50);

INSERT INTO employees
     VALUES (182,'Martha','Sullivan','MSULLIVA','650.507.9878',TO_DATE ('21-JUN-1999', 'dd-MON-yyyy'),'SH_CLERK',2500,NULL,120,50);

INSERT INTO employees
     VALUES (183,'Girard','Geoni','GGEONI','650.507.9879',TO_DATE ('03-FEB-2000', 'dd-MON-yyyy'),'SH_CLERK',2800,NULL,120,50);

INSERT INTO employees
     VALUES (184,'Nandita','Sarchand','NSARCHAN','650.509.1876',TO_DATE ('27-JAN-1996', 'dd-MON-yyyy'),'SH_CLERK',4200,NULL,121,50);

INSERT INTO employees
     VALUES (185,'Alexis','Bull','ABULL','650.509.2876',TO_DATE ('20-FEB-1997', 'dd-MON-yyyy'),'SH_CLERK',4100,NULL,121,50);

INSERT INTO employees
     VALUES (186,'Julia','Dellinger','JDELLING','650.509.3876',TO_DATE ('24-JUN-1998', 'dd-MON-yyyy'),'SH_CLERK',3400,NULL,121,50);

INSERT INTO employees
     VALUES (187,'Anthony','Cabrio','ACABRIO','650.509.4876',TO_DATE ('07-FEB-1999', 'dd-MON-yyyy'),'SH_CLERK',3000,NULL,121,50);

INSERT INTO employees
     VALUES (188,'Kelly','Chung','KCHUNG','650.505.1876',TO_DATE ('14-JUN-1997', 'dd-MON-yyyy'),'SH_CLERK',3800,NULL,122,50);

INSERT INTO employees
     VALUES (189,'Jennifer','Dilly','JDILLY','650.505.2876',TO_DATE ('13-AUG-1997', 'dd-MON-yyyy'),'SH_CLERK',3600,NULL,122,50);

INSERT INTO employees
     VALUES (190,'Timothy','Gates','TGATES','650.505.3876',TO_DATE ('11-JUL-1998', 'dd-MON-yyyy'),'SH_CLERK',2900,NULL,122,50);

INSERT INTO employees
     VALUES (191,'Randall','Perkins','RPERKINS','650.505.4876',TO_DATE ('19-DEC-1999', 'dd-MON-yyyy'),'SH_CLERK',2500,NULL,122,50);

INSERT INTO employees
     VALUES (192,'Sarah','Bell','SBELL','650.501.1876',TO_DATE ('04-FEB-1996', 'dd-MON-yyyy'),'SH_CLERK',4000,NULL,123,50);

INSERT INTO employees
     VALUES (193,'Britney','Everett','BEVERETT','650.501.2876',TO_DATE ('03-MAR-1997', 'dd-MON-yyyy'),'SH_CLERK',3900,NULL,123,50);

INSERT INTO employees
     VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876',TO_DATE ('01-JUL-1998', 'dd-MON-yyyy'),'SH_CLERK',3200,NULL,123,50);

INSERT INTO employees
     VALUES (195,'Vance','Jones','VJONES','650.501.4876',TO_DATE ('17-MAR-1999', 'dd-MON-yyyy'),'SH_CLERK',2800,NULL,123,50);

INSERT INTO employees
     VALUES (196,'Alana','Walsh','AWALSH','650.507.9811',TO_DATE ('24-APR-1998', 'dd-MON-yyyy'),'SH_CLERK',3100,NULL,124,50);

INSERT INTO employees
     VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822',TO_DATE ('23-MAY-1998', 'dd-MON-yyyy'),'SH_CLERK',3000,NULL,124,50);

INSERT INTO employees
     VALUES (198,'Donald','OConnell','DOCONNEL','650.507.9833',TO_DATE ('21-JUN-1999', 'dd-MON-yyyy'),'SH_CLERK',2600,NULL,124,50);

INSERT INTO employees
     VALUES (199,'Douglas','Grant','DGRANT','650.507.9844',TO_DATE ('13-JAN-2000', 'dd-MON-yyyy'),'SH_CLERK',2600,NULL,124,50);

INSERT INTO employees
     VALUES (200,'Jennifer','Whalen','JWHALEN','515.123.4444',TO_DATE ('17-SEP-1987', 'dd-MON-yyyy'),'AD_ASST',4400,NULL,101,10);

INSERT INTO employees
     VALUES (201,'Michael','Hartstein','MHARTSTE','515.123.5555',TO_DATE ('17-FEB-1996', 'dd-MON-yyyy'),'MK_MAN',13000,NULL,100,20);

INSERT INTO employees
     VALUES (202,'Pat','Fay','PFAY','603.123.6666',TO_DATE ('17-AUG-1997', 'dd-MON-yyyy'),'MK_REP',6000,NULL,201,20);

INSERT INTO employees
     VALUES (203,'Susan','Mavris','SMAVRIS','515.123.7777',TO_DATE ('07-JUN-1994', 'dd-MON-yyyy'),'HR_REP',6500,NULL,101,40);

INSERT INTO employees
     VALUES (204,'Hermann','Baer','HBAER','515.123.8888',TO_DATE ('07-JUN-1994', 'dd-MON-yyyy'),'PR_REP',10000,NULL,101,70);

INSERT INTO employees
     VALUES (205,'Shelley','Higgins','SHIGGINS','515.123.8080',TO_DATE ('07-JUN-1994', 'dd-MON-yyyy'),'AC_MGR',12000,NULL,101,110);

INSERT INTO employees
     VALUES (206,'William','Gietz','WGIETZ','515.123.8181',TO_DATE ('07-JUN-1994', 'dd-MON-yyyy'),'AC_ACCOUNT',8300,NULL,205,110);

COMMIT;

These are routine data tests that we do when analyzing data. The working time of these commands can sometimes take some time depending on the size of the data. This is because the underlying reasoning processes have high operating costs and often require access to the whole of the data. Oracle has created an alternate way to rapidly process APPROXIMATE QUERY PROCESSING, depending on the data size of these operations, in order to extend the processing time. (Approximate Query Processing has been introduced with Oracle 12c. With version 18c, Oracle added new functions in this infrastructure.) However, we have to remember that this command is not 100% accurate. This infrastructure can be found quickly and easily with some deviations according to the size of the data.

Now let's examine what the APPROX_COUNT (), APPROX_SUM () and APPROX_RANK () functions do and how they work.

APPROX_COUNT: This function tries to approximate the total number of rows in the query result. If we want to use this function, we must also use the HAVING statement.Otherwise, the query will fail. In addition, we can not use any aggregated function in the query with APPROX_COUNT statement expression. Now let's just see how it works.

The following query returns the 5 most commonly worked jobs in each department.

SELECT  DEPARTMENT_ID,
        JOB_ID, 
        APPROX_COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING APPROX_RANK(PARTITION BY DEPARTMENT_ID ORDER BY APPROX_COUNT(*) DESC) <= 5;

Using the APPROX_COUNT function with MAX_ERROR command, we can also see the difference between the actual value and the approximate value found in the query result.

SELECT  DEPARTMENT_ID,
        JOB_ID, 
        APPROX_COUNT(*) appr_count,
        APPROX_COUNT(*,'MAX_ERROR') appr_error
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING APPROX_RANK(PARTITION BY DEPARTMENT_ID ORDER BY APPROX_COUNT(*) DESC) <= 5;

APPROX_SUM: This function tries to approximate the total sum of rows in the query result. If we want to use this function, we must also use the HAVING statement. Otherwise, the query will fail. In addition, we can not use any aggregated function in the query with APPROX_SUM statement expression. Now let's see how it works. 

The following query returns the highest salary of employees in each department.

SELECT  DEPARTMENT_ID,
        JOB_ID, 
        APPROX_SUM(SALARY) appr_sum
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING APPROX_RANK(PARTITION BY DEPARTMENT_ID ORDER BY APPROX_SUM(SALARY) DESC) <= 1;

Using the APPROX_SUM function with MAX_ERROR command, we can also see the difference between the actual value and the approximate value found in the query result.

APPROX_RANK: This function returns the approximate ordering that will occur in the query result. If we want to use this function, we must also use the HAVING statement. Otherwise, the query will fail. In addition, we can not use any aggregated functions in queries with APPROX_RANK statement. We can use PARTITION BY and ORDER BY statements with APPROX_RANK. However, if you use the ORDER BY statement, this statement must be hosted in the APPROX_RANK or APPROX_SUM statement. Now let's just see how it works.

SELECT department_id, 
       job_id,
       APPROX_RANK(PARTITION BY department_id ORDER BY APPROX_SUM(salary) DESC) appr_rnk
FROM employees
GROUP BY department_id, job_id
HAVING APPROX_RANK(PARTITION BY department_id ORDER BY APPROX_SUM (salary) DESC) <= 2;

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

Topics:
oracle ,oracle database ,sql ,database ,tutorial ,query processing ,query ,processing

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}