1e.24. Compute Instances per Image (OCI360_IMAGES)

#: click on a column heading to sort on it

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 LAUNCH_MODE                                                                                  VARCHAR2(16)
 SIZE_IN_MBS                                                                                  NUMBER
 DISPLAY_NAME                                                                                 VARCHAR2(128)
 TIME_CREATED                                                                                 VARCHAR2(32)
 BASE_IMAGE_ID                                                                                VARCHAR2(4)
 AGENT_FEATURES                                                                               VARCHAR2(4)
 COMPARTMENT_ID                                                                               VARCHAR2(4)
 LAUNCH_OPTIONS$FIRMWARE                                                                      VARCHAR2(8)
 LAUNCH_OPTIONS$NETWORK_TYPE                                                                  VARCHAR2(16)
 LAUNCH_OPTIONS$BOOT_VOLUME_TYPE                                                              VARCHAR2(16)
 LAUNCH_OPTIONS$REMOTE_DATA_VOLUME_TYPE                                                       VARCHAR2(16)
 LAUNCH_OPTIONS$IS_CONSISTENT_VOLUME_NAMING_ENABLED                                           VARCHAR2(8)
 LAUNCH_OPTIONS$IS_PV_ENCRYPTION_IN_TRANSIT_ENABLED                                           VARCHAR2(4)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 OPERATING_SYSTEM                                                                             VARCHAR2(32)
 CREATE_IMAGE_ALLOWED                                                                         VARCHAR2(4)
 OPERATING_SYSTEM_VERSION                                                                     VARCHAR2(32)


WITH t1 as (select /*+ materialize */ * from OCI360_INSTANCES),
     t2 as (select /*+ materialize */ * from OCI360_IMAGES)
SELECT t2.id,
       t2.display_name,
       count(*) total
FROM   t1, t2
WHERE  t1.image_id=t2.id
GROUP BY t2.id, t2.display_name
ORDER BY 3 DESC;


1 rows selected.